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
 General SQL Server Forums
 New to SQL Server Programming
 Need Query Help

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 all
SELECT 2, 0 union all
SELECT 3, 0 union all
SELECT 4, 0 union all
SELECT 1, 50 union all
SELECT 2, 50 union all
SELECT 3, 0 union all
SELECT 1, 20 union all
SELECT 2, 30 union all
SELECT 3, 40 union all
SELECT 4, 0 union all
SELECT 1, 40 union all
SELECT 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 100
3 3 0 100
4 4 0 100
5 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 100
7 3 0 100
8 1 20 90
9 2 30 90
10 3 40 90
11 4 0 90
12 1 40 50
13 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
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2014-08-18 : 05:21:34
corrected the sample data

--
Chandu
Go to Top of Page

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 50
10 3 40 90
11 4 0 90
12 1 40 40 -- Fourth island (restart ack perc)
13 2 10 50



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-18 : 05:34:56
[code]-- SwePeso
WITH 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 cteSource
ORDER BY ID;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

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
Go to Top of Page

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 cteSource
ORDER BY ID;[/CODE]

--
Chandu
Go to Top of Page
   

- Advertisement -