Rank: Administration
Medals: Groups: Administrators
Joined: 10/20/2012 Posts: 106 Points: 327 Location: Tbilisi
Thanks: 0 times Was thanked: 0 time(s) in 0 post(s)
|
BB კოდი:declare @t table (DYear int, V int, nVal money, rem money) insert into @t (DYear,V,nVal) select 2009,1,1.4 union all select 2010,1,1.8 union all select 2011,1,-1.13 union all select 2012,1,1.18 union all select 2013,1,2.2 union all select 2009,2,1.12 union all select 2010,2,1.1 union all select 2011,2,1.05 union all select 2012,2,1.03
;with n as ( select DYear, V, nVal ,row_number() over(partition by V order by DYear asc) as rn from @t ), c as ( select DYear, V, nVal, nVal as rem, rn from n where rn = 1 union all select n.DYear, n.V, n.nVal, n.nVal + c.rem, n.rn from n join c on c.V = n.V and c.rn = n.rn - 1 ) select DYear, V, nVal, rem, rn from c order by V, DYear
;WITH q(DYear, V, nVal, rn) AS (SELECT DYear, V, nVal, ROW_NUMBER()OVER(partition by V ORDER BY DYear) AS rn FROM @t) SELECT q.DYear, q.V, q.nVal, SUM(p.nVal) AS Rem, q.rn FROM q INNER JOIN q AS p ON q.V = p.V and q.DYear >= p.DYear GROUP BY q.DYear, q.v, q.nVal, q.rn ORDER BY q.v, q.DYear -- რეზულტატი: ქუოტა (Quote):DYear V nVal rem rn ----------- ----------- --------------------- --------------------- -------------------- 2009 1 1,40 1,40 1 2010 1 1,80 3,20 2 2011 1 -1,13 2,07 3 2012 1 1,18 3,25 4 2013 1 2,20 5,45 5 2009 2 1,12 1,12 1 2010 2 1,10 2,22 2 2011 2 1,05 3,27 3 2012 2 1,03 4,30 4
(9 row(s) affected)
DYear V nVal Rem rn ----------- ----------- --------------------- --------------------- -------------------- 2009 1 1,40 1,40 1 2010 1 1,80 3,20 2 2011 1 -1,13 2,07 3 2012 1 1,18 3,25 4 2013 1 2,20 5,45 5 2009 2 1,12 1,12 1 2010 2 1,10 2,22 2 2011 2 1,05 3,27 3 2012 2 1,03 4,30 4
(9 row(s) affected)
|