Difference in amount between two consecutive days

can you provide some data? field names?

sample data? sample requested results?

SELECT t1.Dt, t1.Amt, t1.Amt -
    (SELECT TOP 1 t2.Amt
    FROM SomeTable t2
    WHERE t2.Dt < t1.Dt
    ORDER BY t2.Dt DESC) AS Diff
FROM SomeTable t1
ORDER BY t1.Dt ASC

Following are the columns

1. AccID
2. AccNo
3. Amount
4. Date

The table looks like this for a given account and date

AcctID      AcctNo            Amount    Date
100          201022          200          04-01-2010
100          201022          350          03-31-2010
100          201022          175          03-29-2010

and what is the requested results for sample data above?

Are you looking for independent results by AcctID / AcctNo?

It should be like this

AcctID         AcctNo         Amount          Date                     [Difference]
100            201022        175                03-29-2010  
100            201022        350                03-31-2010          175
100            201022        200                04-01-2010          50


Hope this helps...  

SELECT t1.AcctID, t1.AcctNo, t1.Amount, t1.[Date], t1.Amount -
    (SELECT TOP 1 t2.Amout
    FROM SomeTable t2
    WHERE t2.[Date] < t1.[Date] AND t1.AcctID = t2.AcctID AND t1.AcctNo = t2.AcctNo
    ORDER BY t2.[Date] DESC) AS Diff
FROM SomeTable t1
ORDER BY t1.AcctID, t1.AcctNo, t1.[Date]

how u get 50?

I think the last [difference] should be 150, am I right?

My bad, yes it should be 150

try this and let me know the result

select f.AcctID,f.AcctNo,f.Amount,f.Date,f.Amount-s.Amount
from
(
select 
AccID
,AccNo
,Amount
,Date
,row_number() over( partition by AcctNo order by Date desc) as RowNO
from yourtable 
) as f
left outer join 
(
select 
AccID
,AccNo
,Amount
,Date
,row_number() over( partition by AcctNo order by Date desc) as RowNO
from yourtable 
) as s
on f.AccNo=s.AccNo and f.RowNo=s.RowNo-1

Select allOpen in new window

like this

select a.*
      ,Abs(case when b.acctid is null then 0 else a.amount - b.amount) as [Difference]
 from YourTable as A
 Left Outer Join Yourtable as B
   on a.acctid=b.acctid
  and a.acctno = b.acctno
  and a.[date]>B.[Date]
 Where b.date = (select max([date]) from yourtable as C
                   where c.acctid=a.acctid
                     and c.acctno=a.acctno
                     and c.[date] < a.[date]
                )
   and A.Date >= '20100329'
order by 1,2,4  

My original query had a typo.  This is working:


SELECT t1.AcctID, t1.AcctNo, t1.Amount, t1.[Date], t1.Amount -
    (SELECT TOP 1 t2.Amount
    FROM SomeTable t2
    WHERE t2.[Date] < t1.[Date] AND t1.AcctID = t2.AcctID AND t1.AcctNo = t2.AcctNo
    ORDER BY t2.[Date] DESC) AS Diff
FROM SomeTable t1
ORDER BY t1.AcctID, t1.AcctNo, t1.[Date]

BTW, that last difference using the sample data from the question should be -150, not 150.

If we want the absolute value of the difference, and not the difference...


SELECT t1.AcctID, t1.AcctNo, t1.Amount, t1.[Date], ABS(t1.Amount -
    (SELECT TOP 1 t2.Amount
    FROM SomeTable t2
    WHERE t2.[Date] < t1.[Date] AND t1.AcctID = t2.AcctID AND t1.AcctNo = t2.AcctNo
    ORDER BY t2.[Date] DESC)) AS Diff
FROM SomeTable t1
ORDER BY t1.AcctID, t1.AcctNo, t1.[Date]

Try this one

DECLARE @Result TABLE
(
      AccID int,
      AcctNo int,
      Amount int,
      Date DateTime
)

INSERT @REsult VALUES(100,201022,200,'04/01/2010')
INSERT @REsult VALUES(100,201022,350,'03/31/2010')
INSERT @REsult VALUES(100,201022,175,'03/29/2010')

SELECT a.Date, a.Amount, a.Amount -
    (SELECT TOP 1 b.Amount
    FROM @Result b
    WHERE b.Date < a.Date
    ORDER BY b.Date DESC) AS Diff
FROM @Result a
ORDER BY a.Date ASC

dshrivallabh,

Please read the whole thread before posting in a question.  Your post simply rehashes ground already covered in http:#a30053514, almost 10 hours before you posted.

Patrick