Calculation based on prev row - SQL Server Forums

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)

Calculation based on prev row

Author

Topic

shilpash
Posting Yak Master

103 Posts

Posted - 2014-04-30 : 14:02:07

Here's my column looks like--

DATE    A       B        C
1/1    NULL    NULL     300
1/2    200      70     (300+200-70)=430
1/3    300      200    (430+300-200)= 530
1/4     100     100     530+100-100)=530

SO when you look at column c in row 2,,300 is from previous row and adding a-b.Also in row 3,430 comes from row 2 and adding a-b.

Please help.Thanks in advance.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-30 : 14:21:49

Calculating running totals like this is very easy if you are on SQL 2012 or later, but it looks like you are on SQL 2008. There are a few different ways on SQL 2008, none of them that great from a performance perspective, for example see this page: http://www.codeproject.com/Articles/300785/Calculating-simple-running-totals-in-SQL-Server

You can also use a recursive CTE - although I have not experimented with it to see if it is efficient or not. I suspect not.

;WITH cte1 AS
(
	SELECT Date,A, B, ROW_NUMBER() OVER (ORDER BY Date) AS RN
	FROM YourTable
),
cte2 AS
(
	SELECT *,COALESCE(C,0)+COALESCE(A,0) - COALESCE(B,0) AS C
	FROM cte1
	WHERE RN = 1
	
	UNION ALL
	
	SELECT c1.*,
		COALESCE(c2.C,0) + COALESCE(c1.A,0) - COALESCE(c1.B,0)
	FROM
		cte1 c1
		INNER JOIN cte2 c2 ON c2.RN+1 = c1.RN
)
SELECT Date, A, B, C FROM cte2 OPTION (MAXRECURSION  0);

If you find that performance is a problem with any of these methods (which it can be if you have a lot of data), you might want to consider the quirky update - which relies on undocumented features, so I am not recommending it whole-heartedly. Nonetheless, you might want to look it up here: http://www.sqlservercentral.com/articles/T-SQL/68467/

shilpash
Posting Yak Master

103 Posts

Posted - 2014-04-30 : 15:13:50

Hi James,

Thanks for your help but I think we are missing something as I am not getting the desired results--

here's my code--

create TABLE #test(date datetime, A INT,B INT,C INT)

insert INTO #test(date,C) VALUES('10/1/2013',300)

insert INTO #test(date,A,B) VALUES('10/2/2013',200,70)
insert INTO #test(date,A,B) VALUES('10/3/2013',300,200)
insert INTO #test(date,A,B) VALUES('10/4/2013',100,100)

SELECT * FROM #test

;WITH cte1 AS
(
SELECT Date,A, B,C, ROW_NUMBER() OVER (ORDER BY Date) AS RN
FROM #test
),
cte2 AS
(
SELECT *,COALESCE(C,0)+COALESCE(A,0) - COALESCE(B,0) AS C
FROM cte1
WHERE RN = 1

UNION ALL

SELECT c1.*,
COALESCE(c2.C,0) + COALESCE(c1.A,0) - COALESCE(c1.B,0)
FROM
cte1 c1
INNER JOIN cte2 c2 ON c2.RN+1 = c1.RN
)
SELECT Date, A, B, C FROM cte2 OPTION (MAXRECURSION 0);

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-04-30 : 15:31:05

You already have a column named C in the source table. So you need a new name for the newly computed column:

create TABLE #test(date datetime, A INT,B INT,C INT)

insert INTO #test(date,C) VALUES('10/1/2013',300)

insert INTO #test(date,A,B) VALUES('10/2/2013',200,70)
insert INTO #test(date,A,B) VALUES('10/3/2013',300,200)
insert INTO #test(date,A,B) VALUES('10/4/2013',100,100)

SELECT * FROM #test

;WITH cte1 AS
(
SELECT Date,A, B,C, ROW_NUMBER() OVER (ORDER BY Date) AS RN
FROM #test
),
cte2 AS
(
SELECT *,COALESCE(C,0)+COALESCE(A,0) - COALESCE(B,0) AS CNew
FROM cte1
WHERE RN = 1

UNION ALL

SELECT c1.*,
COALESCE(c2.CNew,0) + COALESCE(c1.A,0) - COALESCE(c1.B,0)
FROM
cte1 c1
INNER JOIN cte2 c2 ON c2.RN+1 = c1.RN
)
SELECT Date, A, B, CNew FROM cte2 OPTION (MAXRECURSION 0);

shilpash
Posting Yak Master

103 Posts

Posted - 2014-04-30 : 15:35:06

Awsome.This works

.Thanks for your help James.I really appreciate it.

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-01 : 02:34:14

Just for fun i done it with WHILE LOOP..AS it was not efficient as James one please don't use this(because while loop takes more execution plan)


create TABLE #test(date datetime, A INT,B INT,C INT)
insert INTO #test(date,C) VALUES('10/1/2013',300)
insert INTO #test(date,A,B) VALUES('10/2/2013',200,70)
insert INTO #test(date,A,B) VALUES('10/3/2013',300,200)
insert INTO #test(date,A,B) VALUES('10/4/2013',100,100)

					
DECLARE @Rn1 INT = (SELECT COUNT(*) Rn FROM #test)
DECLARE @Rn2 INT =2
DECLARE @Temp TABLE (date DATETIME,A INT,B INT,C INT)
INSERT INTO @Temp SELECT date,A,B,C FROM (SELECT *,ROW_NUMBER()OVER (ORDER BY date)AS Rn FROM #test )a WHERE Rn =1
WHILE (@Rn2 <>@Rn1+1)
BEGIN
	INSERT INTO @Temp 
	SELECT date,A,B,
	((SELECT C FROM (SELECT *,ROW_NUMBER()OVER (ORDER BY date)AS Rn FROM @Temp )b WHERE b.Rn=@Rn2-1)+a.A-a.B) As C
	FROM (SELECT *,ROW_NUMBER()OVER (ORDER BY date)AS Rn FROM #test )a WHERE Rn = @Rn2
	SET @Rn2 =@Rn2+1
END
SELECT * FROM @Temp

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources