Third row depends on first two rows

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 2008 Forums

Transact-SQL (2008)

Third row depends on first two rows

Author

Topic

shilpash
Posting Yak Master

103 Posts

Posted - 2014-06-04 : 12:33:05

I need to find the difference between first tow rows and put the value in third row--like for eg

Rn fee
1 200
2 300
3
4 500
5 300
6
7 1500
8 1200
9

Output should display as

Rn fee
1 200
2 300
3 100
4 500
5 300
6 200
7 1500
8 1200
9 300

Thanks in advance.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-04 : 13:30:10

[code]DECLARE @Foo TABLE (Rn INT, fee MONEY)

INSERT @Foo VALUES
(1, 200 ),
(2, 300 ),
(3, NULL),
(4, 500 ),
(5, 300 ),
(6, NULL),
(7, 1500),
(8, 1200),
(9, NULL)

SELECT
Base.Rn
,COALESCE(Base.fee, ABS(Prev1.fee - Prev2.fee)) AS Fee
FROM
@Foo AS Base
LEFT OUTER JOIN
@Foo AS Prev1
ON Base.Rn - 1 = Prev1.Rn
AND Base.fee IS NULL
LEFT OUTER JOIN
@Foo AS Prev2
ON Base.Rn - 2 = Prev2.Rn
AND Base.fee IS NULL

[/code]

shilpash
Posting Yak Master

103 Posts

Posted - 2014-06-04 : 16:03:43

Thanks a bunch.This works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources