Subtract Value From Next Record

Nov 5, 2007

Hi,
I am trying to solve the following issue, can anybody throw some lights...
Here is some sample data from the table
TIME STAMP PROJECTID FUNCTION
2007-10-31-01.10.05.00 3333311111 First
2007-10-31-01.10.06.00 3333311111 Second
2007-10-31-01.10.08.00 3333311111 Third
2007-10-31-01.10.10.00 3333311111 Complete
2007-10-31-01.10.11.00 2222244444 First
2007-10-31-01.10.12.00 2222244444 Second
2007-10-31-01.10.14.00 2222244444 Third
2007-10-31-01.10.15.00 2222244444 Complete
... and result I am looking something like below
PROJECT ID FUNCTION DURATION
3333311111 First 0:00:01 (2007-10-31-01.10.06.00 - 2007-10-31-01.10.05.00)
3333311111 Second 0:00:06 (2007-10-31-01.10.08.00 - 2007-10-31-01.10.06.00)
3333311111 Third 0:00:02 (2007-10-31-01.10.10.00 - 2007-10-31-01.10.08.00)
2222244444 First 0:00:01 (2007-10-31-01.10.12.00 - 2007-10-31-01.10.11.00)
2222244444 Second 0:00:02 (2007-10-31-01.10.14.00 - 2007-10-31-01.10.12.00)
2222244444 Third 0:00:01 (2007-10-31-01.10.15.00 - 2007-10-31-01.10.14.00)

hope the question is clear!
thanks for your time,
Murali

View 11 Replies


ADVERTISEMENT

Subtract

Feb 5, 2007

Hi guys

I have a column with datas as
"0.0"
"123.0"
"45.0"
i have a query were i have to match the value only. i don't want the decimal and followed by the any thing.

or
how do i extract just the value not the decimal part so that i can compare it with other value.

how do i do that.
thanks

View 10 Replies View Related

How Do I Subtract Time?

Dec 10, 2007

I have a View in SQL,
SELECT dbo.ATTTblAttendance.LogIn, dbo.ATTTblAttendance.LogOut,
ISNULL(DATEDIFF(Hour, dbo.ATTTblAttendance.LogIn, dbo.ATTTblAttendance.LogOut),0)  AS TimeWorked
FROM dbo.ATTTblAttendance
 
Currently, when Login = 1:00 PM and LogOut = 4:30 PM, TimeWorked = 3.
What I need is for TimeWorked to = 3.5
 
My ASP.NET page has the following:
<asp:TemplateField HeaderText ="Total Hours" >
<ItemTemplate >
<asp:Label ID="TimeWorked" runat="server" Text='<%# Eval("TimeWorked") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
 
 
The goal is to take a person's dailt time in and time out, and subtract their AM and PM break time to yield their TimeWorked.
Ex. time in > 8:00 AM,
      lunch >     :30 minutes
      time out > 4:30 PM
     Total Time Worked today = 8.0 or 7.45, or 7.5, if the time warrant it.
 
How I get this to subtract correctly?
Ayomide

View 3 Replies View Related

Subtract Last Three Characters!

Jun 24, 2004

Hello everyone. I have a query where I pull all item_codes that start with a 7. I need all the item_codes that start with 7, but I need to subtract the last 3 characters from only the item_codes that are not 71860kit or 71851nggun. How would I be able to accomplish this. What I have below subtracts the last 3 characters from all item_codes including 71860kit and 71851nggun. I want these two to stay in tact when I select them in the query.

This is the Query that I have now!

select datepart(year, date_cust_invoice)as INV_YEAR,
datepart(month, date_cust_invoice)as INV_MONTH,
item_code=left(item_code, len(item_code)-3)
, sum(QTY_SALES)as QTY_SALES_TOTALS, sum(SALES_VAL)as SALES_VAL_TOTALS
from opcsahf
where datepart(year, date_cust_invoice) >= '2003'
and substring(item_code, 1, 1) = '7'
group by datepart(year, date_cust_invoice), datepart(month, date_cust_invoice),
item_code
order by inv_year, inv_month, item_code



All help is appreciated!

View 1 Replies View Related

How To Subtract Minutes From The Date?

Oct 18, 2007

I want to fetch all the records which are 2 minutes older
How can I do it?

View 2 Replies View Related

Subtract To Count Columns?

Sep 8, 2013

select Doctor, count(monthyear)- count(paidinfull) as Patients
from tableA
where paidinfull = 'YES'
group by doctor

So I have a table like

Doctors Patients
------ --------
Burns 12
White 7

where Doctors is a group by from tableA and Patients is a result of the row count of paidinfull = yes minus the rowcount of monthyear.

View 5 Replies View Related

Using Case To Subtract To Condition

Sep 30, 2013

I have data for moths which are commulative, for example Feb where month is 02, ecul Jan. + Feb. data. I would like to have the data for only Feb = when month '02' Feb = data for '02' - data for '01'. I would like to writ these commands: For Jan there is no problem to write the command but for other months I am not sure how to write the commands for months other than Jan.

,Jan_qty_1 = case
when [stat_month] = '01' then cast(sum(cast([qty_1_mo] as bigint)) as bigint)
else CAST('0' as int)
end

[Code] ....

here is the data:

ust_code district stat_year stat_monthqty_1_mo
24105220070112619
2410522007022200
2410522007024438
2410522007030
24105220070314434
2410522007042295
2410522007040
2410522007050
2410522007050
241052200706454

View 3 Replies View Related

How To Subtract Days From Given Format

Dec 2, 2014

How to subtract days from the given format...

format : Mon, 03 Nov 2014 14:10:00 GMT

and it is in varchar(50) under dates column the data is inserted

View 1 Replies View Related

How To Subtract Value At Previous Row From Current Row

Oct 1, 2006

Hi all,

I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @ minute 2 plus sales @ minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.
would appreciate any help i can get. thanks

View 5 Replies View Related

Overloading Add/subtract Operators For CLR UDT

Oct 13, 2006

Hi,

I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I€™m trying to use +/-/* operators in T-SQL over my UDT it returns the following error:

Invalid operator for data type. Operator equals add, type equals Vector.

The following fragment does work:

DECLARE @v1 Vector, @v2 Vector, @v3 Vector;



SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)



SELECT @v1 'v1', @v2 'v2', @v1.[Add](@v2) 'v1 + v2'

And this fragment does not work:

DECLARE @v1 Vector, @v2 Vector, @v3 Vector;



SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)



SELECT @v1 'v1', @v2 'v2', @v1+@v2 'v1 + v2'



I guess that SQL Server is not aware of the operators€™ overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @a + @b instead of @a.[Add](@b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field?

Maxim

View 4 Replies View Related

Form To Subtract An Amount From A Table On Sql.

Mar 29, 2008

Hi Guys,
Does anyone have a good example how to subtract an amount from an sql table?
At present i have a table such as this


id

View 2 Replies View Related

How To Subtract 1 Hour From GetDate() Function

Sep 15, 1999

Can any one tell me how to subtracts and add hours to the current date and time?
In my case I have to store the Vancouver date and time in Toronto.
Thanks,
T.Lingam

View 1 Replies View Related

Subtract A Year From Current Date

Jan 12, 2005

Hey all, how do you take the current date and subtract a year from it in a SP?

What I want to do is...

Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.

Edit: the answer is..

dateadd(yyyy, -1, getdate()) as Date1

View 6 Replies View Related

Subtract Values From Different Rows Within Query

Nov 20, 2007

I have a query that uses rollup to get totals and top 101 to restrict the rows to top 100 rows plus the total column. The total column shows total for all rows not just the top 100 which is exactly what i need. The only thing I need now is a row that shows total for all rows that have been excluded, or the total row - total of the top 100.

Here's the query: (Biggest factor i have to keep in mind is query speed.)






Code Block

WITH CTE_name AS (
SELECT TOP 101 b.name,
SUM(ISNULL(CAST(b.launched AS BIGINT),0)) AS quantityPrepared,
SUM(ISNULL(CAST(b.bounced AS BIGINT),0)) AS bounced,
SUM(ISNULL(CAST(b.delivered AS BIGINT),0)) AS delivered
FROM dimension tt
INNER HASH JOIN batch b ON (b.batch_id = tt.batch_id)
WHERE tt.datelaunched >= '11/19/2007'
AND tt.datelaunched < '11/20/2007'
AND tt.id = 1
GROUP BY b.name WITH ROLLUP
ORDER BY SUM(ISNULL(CAST(b.launched AS BIGINT),0)) DESC
)

SELECT
name,
quantityPrepared
bounced,
delivered
FROM CTE_name
ORDER BY 2

View 1 Replies View Related

Problem Subtract 7 Days From Smalldatetime Value

Mar 4, 2008

i get an error whan i do this




Code Snippet
SELECT DATEADD(day, -7, StartDate)AS [sevenDayAgo]
FROM dbo.empList






Msg 517, Level 16, State 2, Line 1

Adding a value to a 'smalldatetime' column caused overflow.

my field StartDate is smalldatetime

TNX

View 5 Replies View Related

How To Subtract Days From Date Variable In Sql Query?

Mar 12, 2007

Hi,
I've searched quite a bit for help with this syntax but have given up.
I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date.  I have tried this and it's incorrect syntax:
WHERE   (fldDate < ({ fn NOW() - 500 })
Can someone please help me out with correct syntax for this?  thanks much.
 

View 3 Replies View Related

Transact SQL :: Subtract Two Columns With Windows Function

Dec 5, 2015

How can i subtract two columns:

I have table:
       
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2

But to continue for each row and value is reduced by the previously value.

My output should be like as:

ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100

View 3 Replies View Related

T-SQL (SS2K8) :: Today - Subtract 30 Days From Date Field

Feb 4, 2015

We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.

select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())

In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())

For both we get the following error "Arithmetic overflow error converting expression to data type datetime."

View 7 Replies View Related

Shopping Cart, How Do I Subtract The Quantity Purchased From The Stock In Database?

Dec 11, 2007

Im making a shopping cart website for a school project in ASP.net with VB. I need help subtracting the quantity purchased (its saved in a session) from the stock number saved in a database.I know this:UPDATE inventory SET stock = stock - <quantity_purchased> WHERE id = <inventory_id>But I dont understand how to get the quantity purchased from the session to <quantity_purchased>. I tried putting the name of the session there and I got an error, i tried saving the session into a dim didnt work either.
 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [stock] FROM [product]" InsertCommand="INSERT INTO [product] ([stock]) VALUES (@stock)" UpdateCommand="UPDATE product SET stock = (stock - @Quantity) WHERE (productID = @productID)">
<InsertParameters>
<asp:Parameter Name="stock" Type="Int16" />
</InsertParameters>
<UpdateParameters>
<asp:SessionParameter Name="Quantity" SessionField="Quantity" Type="Int32" />
<asp:SessionParameter Name="productID" SessionField="productID" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
 and I have than in my VB code on submit : SqlDataSource1.Update()

View 1 Replies View Related

SQL Server 2012 :: How To Subtract A Column From A Query Result And Add Difference To Another

Mar 20, 2015

I have a query which provides the below result set:

1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013

I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.

1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80

[code]...

View 5 Replies View Related

SQL Server 2008 :: How To Subtract Dates From Previous Date On Same Patient

Jun 22, 2015

I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.

create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')

[code]....

View 9 Replies View Related

SQL Server 2012 :: Subtract / Exclude Value Items From A Column And Add It To Another Column In Same Table

May 26, 2014

I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.

View 1 Replies View Related

Transact SQL :: SUM Of Two Table Column Base On Another Column Value And SUBTRACT And Join Tables

Oct 14, 2015

I have the following table

Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
    1             |John                       |Baker
    2             |Carl                        |Lennon
    3             |Marion                    |Herbert

Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
    1        |1/1/14      |12/30/14
    2        |1/1/15      |12/30/15

[code]...

I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID  AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID  AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID  AS SickBalance

and the table should be shown as below after executing the query

EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance

View 4 Replies View Related

How To Subtract Previous Value Of One Column From Current Value Of Another Column

Sep 7, 2006

I have a table like this:

Bottom Top

0 2

4 6.5

9 14

15 17

In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.

So, the result would be 0, 2, 2.5, 1

I must return the value in a table. Thank you all for your help!

Karen

View 9 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different &&amp; Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

Ways To Make This Work: Several Selectable Related Record For One Main Record.

Apr 6, 2007

Hey all!



Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.

To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.

On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.



I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.



Thanks in advance,

Johan

View 5 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

SSIS: Multi-Record File Extract With 9 Record Types

Feb 26, 2008

I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have been able to create a sample file with two of my record types.

I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.

does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?

Thanks in advance for any help you might be able to provide.


View 3 Replies View Related

Add Date To Record In SQL Server Each Time Record Is Added

Mar 1, 2006

Hi
 
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks

View 6 Replies View Related

Restrict Inserting Record If Record Already Exist In Table

Apr 17, 2014

Is that possible to restrict inserting the record if record already exist in the table.

Scenario: query should be

We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.

View 2 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View 3 Replies View Related

Switch Record Background Color With Each Record In Report

Jan 14, 2008

Hi Everyone-

i have a matrix report
and i want to switch the record background color with each record in the value column in that matrix report
e.g 1st record background color is gray and next record background color is white
and then the next record background color is gray ... and so on

can anyone help?

thanx
Maylo

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved