CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
peter
peter

Reputation: 2516

Difference of data per day

I have a table called Addim and the data looks like:

TName           Idate        Number
Integrated      3/21/2012    26984013
Integrated      3/20/2012    26959226
Integrated      3/19/2012    26933190

I want the output as:

Idate        Diff
3/21/2012    24787
3/20/2012    26036

I did something like this:

Select Count(*),Idate
from dbo.Addim
group by Idate

But i am getting output like this:

Idate        Diff
03/21/2012   1
03/20/2012   1

basically what it does is it takes the difference from previous day for example: for 3/21/2012 the diff is 26984013(3/21/2012)-26959226(3/20/2012) = 24787 and for
3/20/2012 is 26959226(3/20/2012)-26933190(3/19/2012) = 26036

Upvotes: 2

Views: 69

Answers (2)

Lamak
Lamak

Reputation: 70648

Try this:

;WITH CTE AS
(
    SELECT TName, Idate, Number, ROW_NUMBER() OVER(ORDER BY Idate) Corr
    FROM #Temp1--YourTable
)
SELECT A.Idate, A.number - B.number Diff
FROM CTE A
INNER JOIN CTE B
ON A.Corr = B.Corr + 1

This assumes one record per day, but it will work even if there are missing days.

Upvotes: 3

KM.
KM.

Reputation: 103607

the trick is to join the table back to itself for the previous day, like this:

DECLARE @Addim table (TName varchar(10), Idate datetime,Number int)
INSERT INTO @Addim VALUES ('Integrated','3/21/2012',26984013)
INSERT INTO @Addim VALUES ('Integrated','3/20/2012',26959226)
INSERT INTO @Addim VALUES ('Integrated','3/19/2012',26933190)


SELECT
    a.TName,a.Idate, a.Number-b.Number
    FROM @Addim             a
        INNER JOIN @Addim   b ON a.TName=b.TName AND a.Idate=b.Idate+1

OUTPUT:

TName      Idate                   
---------- ----------------------- -----------
Integrated 2012-03-21 00:00:00.000 24787
Integrated 2012-03-20 00:00:00.000 26036

(2 row(s) affected)

I wasn't sure the significance of TName, so I joined on that column too, assuming that you'd have multiple different values there as well. You can easily remove it from the join if it is not used like that.

Upvotes: 3

Related Questions