Save previous row value to next row..

Question

Having..

ID Date N1 N2
1 2009-01-01 00:00:00.000 100 99
2 2009-01-02 00:00:00.000 0 0
3 2009-01-03 00:00:00.000 0 0
4 2009-01-04 00:00:00.000 0 0
5 2009-01-05 00:00:00.000 0 0

Output needed..

ID Date N1 N2
1 2009-01-01 00:00:00.000 100 99
2 2009-01-02 00:00:00.000 99 80
3 2009-01-03 00:00:00.000 80 70
4 2009-01-04 00:00:00.000 70 50
5 2009-01-05 00:00:00.000 50 40

Without using cursors.

Suthish Nair · Answer

I also posted same thread to asp.net Forums. I got reply from an MVP. Like to share here. http://forums.asp.net/t/1666258.aspx/1?Save+previous+row+value+to+next+row+

Suthish Nair · Answer

CREATE TABLE [dbo].[testtable]( [A] [nvarchar](max) NULL [B] [numeric](18 0) NULL [Date_Val] [date] NULL ) insert into testtable select 'A' 15 '2011-03-01' union all select'B' 20 '2011-03-02' union all select'C' 25 '2011-03-03' union all select'D' 30 '2011-03-04' union all select'E' 35 '2011-03-05' WIth CTE (Date_valAB) AS ( SELECT s.Date_Val s.A sl.B AS B FROM testtable s OUTER APPLY (SELECT SUM(B) AS B FROM testtable WHERE date_val = s.date_val AND A = s.A ) sl ) SELECT c1.Date_val c1.A c1.B ISNULL(c2.TotalPrev0) AS OpeningStock ISNULL(c1.B0) AS ClosingStock FROM CTE c1 OUTER APPLY(SELECT SUM(B) AS TotalPrev FROM CTE WHERE Date_val = DATEADD(day-1c1.Date_val) ) c2

Suthish Nair · Answer

anyone :(