Difference in amount between two consecutive days

Hi Experts,

I have a table that holds the transaction values, I need a query that will give me the absolute difference between each day amount for every month.

Thanks
LVL 1
SamoinAsked:
Who is Participating?
 
Patrick MatthewsCommented:
Testing it, I used...


CREATE TABLE SomeTable (AcctID int, AcctNo Int, Amount int, Date datetime)

INSERT INTO SomeTable (AcctID, AcctNo, Amount, Date) VALUES (100, 201022, 200, '2010-04-01')
INSERT INTO SomeTable (AcctID, AcctNo, Amount, Date) VALUES (100, 201022, 350, '2010-03-31')
INSERT INTO SomeTable (AcctID, AcctNo, Amount, Date) VALUES (100, 201022, 175, '2010-03-29')
INSERT INTO SomeTable (AcctID, AcctNo, Amount, Date) VALUES (101, 201023, 200, '2010-04-01')
INSERT INTO SomeTable (AcctID, AcctNo, Amount, Date) VALUES (102, 201024, 200, '2010-04-01')
INSERT INTO SomeTable (AcctID, AcctNo, Amount, Date) VALUES (102, 201024, 100, '2010-04-02')

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]

That returned the results below:

AcctID  AcctNo   Amount  Date                       Diff
--------------------------------------------------------
100     201022   175     2010-03-29 00:00:00.000    NULL
100     201022   350     2010-03-31 00:00:00.000    175
100     201022   200     2010-04-01 00:00:00.000    -150
101     201023   200     2010-04-01 00:00:00.000    NULL
102     201024   200     2010-04-01 00:00:00.000    NULL
102     201024   100     2010-04-02 00:00:00.000    -100

Open in new window

0
 
bchoorCommented:
can you provide some data? field names?
0
 
Reza RadConsultant, TrainerCommented:
sample data? sample requested results?
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Patrick MatthewsCommented:
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
0
 
SamoinAuthor Commented:
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
0
 
Reza RadConsultant, TrainerCommented:
and what is the requested results for sample data above?
0
 
Patrick MatthewsCommented:
Are you looking for independent results by AcctID / AcctNo?
0
 
SamoinAuthor Commented:
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...  
0
 
Patrick MatthewsCommented:
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]
0
 
bchoorCommented:
how u get 50?
0
 
Reza RadConsultant, TrainerCommented:
I think the last [difference] should be 150, am I right?
0
 
SamoinAuthor Commented:
My bad, yes it should be 150
0
 
Reza RadConsultant, TrainerCommented:
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

Open in new window

0
 
LowfatspreadCommented:
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  
0
 
Patrick MatthewsCommented:
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]
0
 
Patrick MatthewsCommented:
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]
0
 
dshrivallabhCommented:
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
0
 
Patrick MatthewsCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.