Need Query help in SQL Server [Resolved]

Posted by Bandi under Sql Server on 8/18/2014 | Points: 10 | Views : 1390 | Status : [Member] [MVP] | Replies : 1
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, 50 union all

SELECT 2, 10



/*Expected OUTPUT
ID LevelId Percentage SumOf%
1 1 100 100
2 2 0 100
3 3 0 100
4 4 0 100
5 1 50 100
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 50 60
13 2 10 60*/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif



Responses

Posted by: Bandi on: 8/19/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down

Resolved
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, 50 union all

SELECT 2, 10

SELECT ID, LevelId, Percentage, SUM(Percentage) OVER(PARTITION BY GrpID) 'SumOf%' FROM ( SELECT *, ROW_NUMBER() OVER(ORDER BY ID)-LevelID GrpID FROM @t)temp



/* OUTPUT
ID LevelId Percentage SumOf%
1 1 100 100
2 2 0 100
3 3 0 100
4 4 0 100
5 1 50 100
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 50 60
13 2 10 60*/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Bandi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response