May 20, 2013 at 8:49 pm
Dear Team ,
Kindly advise , How to create query in sqlserver for below issue,
I Have Table with below details
Id Name Value1 value2 Total(Value1+Value2) PreviousRowId
1 A 10 0 10 0
2 B 5 1
3 C 6 2
4 D 7 3
5 E 8 4
6 F 5 5
Qustions :
1 . 1st row Total comes to second row Value1
then calculate 2nd row total . then its comes to 3rd row Value1 column. and its continue for all rows.
how to write the query for this issue?
Note : Link between all rows ==> Id , PreviousRowId
I need output like this below ,
Id Name Value1 value2 Total(Value1+Value2) PreviousRowId
1 A 10 0 10 0
2 B 10 5 15 1
3 C 15 6 21 2
4 D 21 7 28 3
5 E 28 8 36 4
6 F 36 5 41 5
Thanks,
Chandrahasan S
May 21, 2013 at 2:05 pm
Please visit the following link to see how to best provide data for your questions:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455
May 22, 2013 at 1:04 am
You will need a CTE to achieve the results
DECLARE@table TABLE
(
IdINT,
NameVARCHAR(10),
Value1INT,
value2INT,
PreviousRowId INT
)
INSERT@table
SELECT1, 'A', 10, 0, 0 UNION ALL
SELECT2, 'B', 0, 5, 1 UNION ALL
SELECT3, 'C', 0, 6, 2 UNION ALL
SELECT4, 'D', 0, 7, 3 UNION ALL
SELECT5, 'E', 0, 8, 4 UNION ALL
SELECT6, 'F', 0, 5, 5
; WITH cte_table AS
(
SELECTt.Id, t.Name, t.Value1, t.value2, t.Value1 + t.value2 AS total, t.PreviousRowId
FROM@table AS t
WHEREt.PreviousRowId = 0
UNION ALL
SELECTt2.Id, t2.Name, ct.total, t2.value2, ct.total + t2.value2 AS total, t2.PreviousRowId
FROM@table AS t2
INNER JOIN cte_table AS ct ON t2.PreviousRowId = ct.Id
)
SELECT*
FROMcte_table
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply