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)
|
