当前位置: 动力学知识库 > 问答 > 编程问答 >

TSQL 2008 Running total with previous months & years

问题描述:

I'm trying to get the Running total from the values from the previous months. And if the month is january I would like to get the total from the previous year and so forth.

I hope someone can help.

WITH CTE AS (SELECT COUNT(LZP.pv_zaaknummer) AS [Aantal LZP]

, YEAR(LZP.lzp_actief_vanaf) AS Jaar

, MONTH(LZP.lzp_actief_vanaf) AS Maand

FROM dm.crm_LZP_Vn_zaaktype_leefzorgplan_registrerenExtensionBase_hist AS LZP

WHERE LZP.LZP_actief_tot_LDTS > GETDATE()

GROUP BY YEAR(LZP.lzp_actief_vanaf)

, MONTH(LZP.lzp_actief_vanaf)

)

SELECT a.Jaar

, a.Maand

, a.[Aantal LZP]

, (

SELECT SUM(b.[Aantal LZP])

FROM CTE AS b

WHERE b.Jaar <= a.Jaar

) AS [Running Total 1]

, (

SELECT SUM(b.[Aantal LZP])

FROM CTE AS b

WHERE b.Jaar <= a.Jaar

AND b.Maand <= a.Maand

) AS [Running Total 2]

FROM CTE AS a

ORDER BY a.Jaar, a.Maand;

Results as of now, I'm getting the Totals per year and then the running totals per year:

Jaar Maand Aantal LZP Running Total 1 Running Total 2

2014 4 11 661 11

2014 5 52 661 63

2014 6 70 661 133

2014 7 76 661 209

2014 8 39 661 248

2014 9 86 661 334

2014 10 112 661 446

2014 11 120 661 566

2014 12 95 661 661

2015 1 57 3327 57

2015 2 109 3327 166

2015 3 196 3327 362

2015 4 200 3327 573

2015 5 169 3327 794

2015 6 233 3327 1097

2015 7 276 3327 1449

2015 8 224 3327 1712

2015 9 203 3327 2001

2015 10 291 3327 2404

2015 11 296 3327 2820

2015 12 412 3327 3327

2016 1 311 6062 368

2016 2 341 6062 818

2016 3 476 6062 1490

2016 4 440 6062 2141

2016 5 418 6062 2780

2016 6 500 6062 3583

2016 7 249 6062 4184

I would like it to be:

Running Total 3

11

63

133

209

248

334

446

566

661

718

827

1023

1223

1392

1625

1901

2125

2328

2619

2915

3327

3638

3979

4455

4895

5313

5813

6062

网友答案:

you could do it without subquery try

....................)

    SELECT a.Jaar
    , a.Maand
    , a.[Aantal LZP],SUM([Aantal LZP]) over (order by jaar) [Running Total 1]
    ,   SUM([Aantal LZP]) over (partition by jaar order by maand) [Running Total 2],
     SUM([Aantal LZP]) over ( order by jaar,maand) [Running Total 3]

    FROM   CTE AS a
    ORDER BY a.Jaar, a.Maand;

old version

SELECT a.Jaar
, a.Maand
, a.[Aantal LZP],(SELECT SUM(b.[Aantal LZP])
    FROM CTE  AS b
    WHERE b.Jaar <= a.Jaar
    ) [Running Total 1],
    (SELECT SUM(b.[Aantal LZP])
    FROM CTE  AS b
    WHERE b.Jaar <= a.Jaar and Maand<=a.Maand
    ) [Running Total 2]
,   (SELECT SUM(b.[Aantal LZP])
    FROM CTE  AS b
    WHERE dateadd(year,jaar-1900,dateadd(month,maand-1,0)) <= dateadd(year,a.jaar-1900,dateadd(month,a.maand-1,0))
    ) [Running Total 3]

FROM   CTE  AS a
ORDER BY a.Jaar, a.Maand;
网友答案:

Found the Solution but now I must find to integrate it so it responds to my reportparameters Jaar and Maand

DECLARE @SalesTbl TABLE (Jaar int, Maand int, Aantal int, RunningTotal int)
DECLARE @Jaar int,
        @Maand int,
        @Aantal int,
        @RunningTotal int
SET @RunningTotal = 0
DECLARE rt_cursor CURSOR
FOR

WITH CTE AS 
(SELECT COUNT(LZP.pv_zaaknummer) AS [Aantal LZP]
, YEAR(LZP.lzp_actief_vanaf) AS Jaar
, MONTH(LZP.lzp_actief_vanaf) AS Maand
FROM dm.crm_LZP_Vn_zaaktype_leefzorgplan_registrerenExtensionBase_hist AS LZP
WHERE LZP.LZP_actief_tot_LDTS > GETDATE()
GROUP BY LZP.lzp_actief_vanaf
)


SELECT A.Jaar, A.Maand, SUM(A.[Aantal LZP])
FROM CTE AS A
GROUP BY Jaar, Maand
ORDER BY A.Jaar, A.Maand
OPEN rt_cursor
FETCH NEXT FROM rt_cursor INTO @Jaar, @Maand, @Aantal
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @RunningTotal = @RunningTotal + @Aantal
 INSERT @SalesTbl VALUES (@Jaar, @Maand,@Aantal,@RunningTotal)
 FETCH NEXT FROM rt_cursor INTO @Jaar, @Maand, @Aantal
 END

CLOSE rt_cursor

DEALLOCATE rt_cursor

SELECT * FROM @SalesTbl
分享给朋友:
您可能感兴趣的文章:
随机阅读: