Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Kindly advise , How to create query in sqlserver for below issue, Expand / Collapse
Author
Message
Posted Monday, May 20, 2013 8:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:59 PM
Points: 33, Visits: 77
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
Post #1454791
Posted Tuesday, May 21, 2013 2:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 8, 2013 7:16 PM
Points: 221, Visits: 132
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/
You can also follow my twitter account to get daily updates: @BLantz2455
Post #1455211
Posted Wednesday, May 22, 2013 1:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
You will need a CTE to achieve the results

DECLARE	@table TABLE
(
Id INT,
Name VARCHAR(10),
Value1 INT,
value2 INT,
PreviousRowId INT
)

INSERT @table
SELECT 1, 'A', 10, 0, 0 UNION ALL
SELECT 2, 'B', 0, 5, 1 UNION ALL
SELECT 3, 'C', 0, 6, 2 UNION ALL
SELECT 4, 'D', 0, 7, 3 UNION ALL
SELECT 5, 'E', 0, 8, 4 UNION ALL
SELECT 6, 'F', 0, 5, 5

; WITH cte_table AS
(
SELECT t.Id, t.Name, t.Value1, t.value2, t.Value1 + t.value2 AS total, t.PreviousRowId
FROM @table AS t
WHERE t.PreviousRowId = 0
UNION ALL
SELECT t2.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 *
FROM cte_table




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1455325
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse