Difference between one row and the previous row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Difference between one row and the previous row

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-23 : 10:39:49

Oz writes "Hey guys,

I have a question for you

Given below is my table in sql server... if I want to calculate the difference between one row and the previous row based on the transaction date how could I do that.

Num.    catcode             dsc       trans_date                     trans_mth          clm_amt

1          A01070             clm1     2000-01-01 00:00:00       01                     2000
2          A01070             clm1     2000-01-03 00:00:00       01                     3000
3          A01070             clm1     2000-01-15 00:00:00       01                     4000
4          A01070             clm1     2000-02-05 00:00:00       02                     1000
5          A01070             clm2     2000-03-05 00:00:00       03                     1000

Hint: I have done this based on the number and the sql is as below... but, I can't get my head around getting this working based on the transaction date

select t1.num, t1.catcode, t1.dsc, t1.trans_date, t1.trans_mth,
sum(t1.clm_amt) clm_amt, sum(t1.clm_amt-isnull(t2.clm_amt,0)) new_clm_amt
from
clm_test t1 left outer join clm_test t2 on
t1.num = t2.num+1
and t1.trans_mth = t2.trans_mth
group by t1.num, t1.catcode, t1.dsc, t1.trans_date, t1.trans_mth
order by t1.num, t1.catcode, t1.dsc, t1.trans_date, t1.trans_mth

cheers for ur help guys"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-06-23 : 11:15:57

maybe this:

select t1.num, t1.catcode, t1.dsc, t1.trans_date, t1.trans_mth,
sum(t1.clm_amt) clm_amt, sum(t1.clm_amt-isnull(t2.clm_amt,0)) new_clm_amt
from
clm_test t1 left outer join clm_test t2 on
t1.trans_date = DATEADD(day, 1, t2.trans_date)
and t1.trans_mth = t2.trans_mth
group by t1.num, t1.catcode, t1.dsc, t1.trans_date, t1.trans_mth
order by t1.num, t1.catcode, t1.dsc, t1.trans_date, t1.trans_mth

and why do you need trans_mth when you have it in the trans_date column??

Go with the flow & have fun! Else fight the flow :)

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-06-23 : 17:53:54

I think that you could use:

SELECT a.trans_date, a.clm_amount - b.clm_amount
   FROM clm_test AS a
   JOIN clm_test AS b
      ON (b.clm_date = (SELECT Max(c.clm_date)
         FROM clm_test AS c
         WHERE c.clm_date < a.clm_date))

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources