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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bchoorCommented:
can you provide some data? field names?
Reza RadConsultant, TrainerCommented:
sample data? sample requested results?
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
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

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

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  
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]
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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]
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
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.