Hello All

I have to implement a query like this .I have a table structure like below.Col1,Col2,Col3 are columns in table Col4 is a derived Column which has to be calculated in SQl .I dont want to do a row by row processing.Was wondering if there is a way to do in set based operation .

Col4 Logic is Col2+Col3 If Col2=0 Then use Previous Value of Col4 

Example 

Row1 Col4 --10+20--30

Row2--Col4--As Col2 for row2 is 0 use row1 Col4+Col3(10) which is 30+10--40

Row3--As Col2 is 0 use Row 2 Col4 which is 50+ Col3 value which is 5 total is 45

Row 4 As there is value for col2=20 use col2+col3

This should do what you're trying to do.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;

CREATE TABLE #temp (
	Col1 INT,
	Col2 INT,
	Col3 INT
	);
INSERT #temp (Col1,Col2,Col3) VALUES
(1,10,20),
(2,0,10),
(3,0,5),
(4,20,10),
(5,0,5);

WITH GetGroup AS (
	SELECT
		t.Col1,
		t.Col2,
		t.Col3,
		CAST(SUBSTRING(MAX(CAST(t.Col1 AS BINARY(4)) + CAST(NULLIF(t.Col2, 0) AS BINARY(4))) OVER (ORDER BY t.Col1 ROWS UNBOUNDED PRECEDING), 5, 8) AS INT) AS C2_Group
	FROM
		#temp t
)
	SELECT
		gg.Col1,
		gg.Col2,
		gg.Col3,
		SUM(gg.Col2 + gg.Col3) OVER (PARTITION BY gg.C2_Group ORDER BY gg.Col1) AS Col4
	FROM
		GetGroup gg
	ORDER BY 
		gg.Col1

The results...

Col1        Col2        Col3        Col4
----------- ----------- ----------- -----------
1           10          20          30
2           0           10          40
3           0           5           45
4           20          10          30
5           0           5           35

HTH,

Jason

One way

Declare @Sample Table(Col1 int, Col2 int, Col3 int);
Insert @Sample(Col1, Col2, Col3) Values
(1, 10, 20),
(2, 0, 10),
(3, 0, 5),
(4, 20, 10),
(5, 0, 5);

Declare @Rn Table(Col1 int, Col2 int, Col3 int, rn int Primary Key);

;With cteRn As
(Select Col1, Col2, Col3, Row_Number() Over(Order By Col1) As rn
From @Sample)
Insert @Rn(Col1, Col2, Col3, rn)
Select Col1, Col2, Col3, rn
From cteRn;

;With cteCol4 As
(Select Col1, Col2, Col3, rn, Col2+Col3 As Col4 
From @Rn
Where rn = 1
Union All
Select r.Col1, r.Col2, r.Col3, r.rn, Case When r.Col2 = 0 Then r.Col3 + c.Col4 Else r.Col2 + r.Col3 End  As Col4
From cteCol4 c
Inner Join @Rn r On c.rn + 1 = r.rn)
Select Col1, Col2, Col3, Col4
From cteCol4
Order By Col1;

Tom

• Marked as answer by SPRRAc 1 hour 13 minutes ago

Thanks Jason .That was Extraordinary 

Thanks Tom.Really Appreciate it

This should do what you're trying to do.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;

CREATE TABLE #temp (
	Col1 INT,
	Col2 INT,
	Col3 INT
	);
INSERT #temp (Col1,Col2,Col3) VALUES
(1,10,20),
(2,0,10),
(3,0,5),
(4,20,10),
(5,0,5);

WITH GetGroup AS (
	SELECT
		t.Col1,
		t.Col2,
		t.Col3,
		CAST(SUBSTRING(MAX(CAST(t.Col1 AS BINARY(4)) + CAST(NULLIF(t.Col2, 0) AS BINARY(4))) OVER (ORDER BY t.Col1 ROWS UNBOUNDED PRECEDING), 5, 8) AS INT) AS C2_Group
	FROM
		#temp t
)
	SELECT
		gg.Col1,
		gg.Col2,
		gg.Col3,
		SUM(gg.Col2 + gg.Col3) OVER (PARTITION BY gg.C2_Group ORDER BY gg.Col1) AS Col4
	FROM
		GetGroup gg
	ORDER BY 
		gg.Col1

The results...

Col1        Col2        Col3        Col4
----------- ----------- ----------- -----------
1           10          20          30
2           0           10          40
3           0           5           45
4           20          10          30
5           0           5           35

HTH,

Jason