difference between 2 values in the same column of a table

Question

Post reply

difference between 2 values in the same column of a table

kalpana_majumder

SSC Enthusiast

Points: 140
More actions
June 24, 2008 at 5:00 am

#183964

Hi,
I have a history table where few column are avl.
ID
loan_num
line_amt
line_change_date
Sample data as follows:
ID loan_num line_amt line_change_date
I001 1234 100000 2008-06-23
I002 1234 50000 2007-05-02
I003 1234 25000 1997-02-28
I may have multiple values for the same loan_num
I need the difference between the adjacent line amounts for the same loan number.
My output should be :
ID loan_num line_amt line_change_date aggr_amt
I001 1234 100000 2008-06-23 50000
I002 1234 50000 2007-05-02 25000
I003 1234 25000 1997-02-28 25000
I003 -> the value same as line_amt
I002-> line_amt of I002-line_amt of I002
I001 -> line_amt of I002 - line_amt of I001
pls revert ASAP

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4663 More actions · Answer 1

You will find your answer here

Linking to the Previous Row

http://www.sqlservercentral.com/articles/T-SQL/62159/

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

kalpana_majumder SSC Enthusiast Points: 140 More actions · Answer 2

kalpana_majumder

SSC Enthusiast

Points: 140

June 24, 2008 at 5:56 am

#833252

will it work in SQL 2000?

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4663 More actions · Answer 3

Unfortunately, no. Row_number() function was launched in SQL 2005

You will have to find another ways for SQL 2000

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 4

You can use a similar method in 2k, but you'll need to insert into a temporary table that has an IDENTITY column so that you get sequential numbers.

What you're looking for is a running total... see the following article for a very high speed method that works in 2k and 2k5...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)