Author |
Topic |
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-08-18 : 03:50:36
|
[CODE]declare @t table( id int identity(1,1), LevelId int, Percentage int) insert @t(LevelId, Percentage)SELECT 1, 100 union allSELECT 2, 0 union allSELECT 3, 0 union allSELECT 4, 0 union allSELECT 1, 50 union allSELECT 2, 50 union allSELECT 3, 0 union allSELECT 1, 20 union allSELECT 2, 30 union allSELECT 3, 40 union allSELECT 4, 0 union allSELECT 1, 40 union allSELECT 2, 10 /* OUTPUT id LevelId Percentage SumOf%1 1 100 100 here Sum up the percentage column for sequential Levels are 1,2, 3, 4 2 2 0 1003 3 0 1004 4 0 1005 1 50 100 the sum value should start for next LevelID = 1 and here Sum up the percentage column for sequential Levels are 1,2, 3 6 2 50 1007 3 0 1008 1 20 909 2 30 9010 3 40 9011 4 0 9012 1 40 5013 2 10 50 */[/CODE]am so sorry for the incorrect sample data.. corrected the same above. --Chandu |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-18 : 04:06:05
|
How are we supposed to know the business rules for calculating the "SumOf%" column?Also, where is the row with 80 for percentage in your result? It says 40 later but 80 in the insert. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-08-18 : 05:21:34
|
corrected the sample data--Chandu |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-18 : 05:30:28
|
Isn't this the correct output? 1 1 100 100 -- First island (restart ack perc) 2 2 0 100 3 3 0 100 4 4 0 100 5 1 50 50 -- Second island (restart ack perc) 6 2 50 100 7 3 0 100 8 1 20 20 -- Third island (restart ack perc) 9 2 30 5010 3 40 9011 4 0 9012 1 40 40 -- Fourth island (restart ack perc)13 2 10 50 Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-08-18 : 05:34:56
|
[code]-- SwePesoWITH cteSource(ID, LevelID, Percentage, Island)AS ( SELECT ID, LevelID, Percentage, ROW_NUMBER() OVER (ORDER BY ID) - LevelID AS Island FROM @t)SELECT ID, LevelID, Percentage, SUM(Percentage) OVER (PARTITION BY Island ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [SumOf%]FROM cteSourceORDER BY ID;[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-08-18 : 07:20:08
|
As my DB version is 2008, i can not make use of 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'--Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2014-08-18 : 07:25:44
|
Thanks... made changes for above query to work in MSSQL 2008[CODE];WITH cteSource(ID, LevelID, Percentage, Island)AS ( SELECT ID, LevelId, Percentage, ROW_NUMBER() OVER (ORDER BY ID) - RiskLevelId AS Island FROM @t)SELECT ID, LevelID, Percentage, SUM(Percentage) OVER (PARTITION BY Island) AS [SumOf%] FROM cteSourceORDER BY ID;[/CODE]--Chandu |
|
|
|
|
|