In dit artikel laat ik een manier zien om in SQL Server een voortschrijdend totaal over 4 weken te berekenen. Er leiden meer wegen naar Rome, maar dit is een manier die werkt met een datumdimensie.

Wat bedoel ik met een voortschrijdend totaal over 4 weken? Het is een som of gemiddelde, berekend over een aantal weken. Het getal schuift een week terug en pakt dan de volgende vier weken. In het voorbeeld hieronder nemen we week 37-40, dat noemen we blok 1. Dan gaan we een week terug in het verleden en nemen we week 36-39, dat noemen we blok 2. Enzovoort.

Sql_4Week_Moving_total

Waarom is dit handig? Dit voortschrijdende 4-wekentotaal kun je in een dashboard gebruiken voor een trendlijn of sparkline van belangrijke bedrijfsgegevens.

De eerste stap is bepalen hoeveel “groepen” van 4 weken data je nodig hebt. In mijn voorbeeld heb ik vier groepen weken, blok 1 - blok 4. Voor het SQL-voorbeeld gebruik ik zes blokken. De weken moeten in de tijd meeschuiven, zonder dat er iets hardcoded in staat, want het dashboard toont data tot en met de laatste volledige week.

Stap 1: Bepaal de laatste volledige week

SQL
DECLARE @EindWeek AS INT = (SELECT MIN(WeekNummer) FROM dbo.DimDatum WHERE DatumID = CONVERT(VARCHAR, DATEADD(dd,-7,GETDATE()),112) )

Stap 2: Declareer blokken van weken

SQL
DECLARE @StartWeekBlok6 VARCHAR(6) -- (EindWeek -3 Weken) (dit is het laatste blok)
DECLARE @EindWeekBlok6 VARCHAR(6)
DECLARE @StartWeekBlok5 VARCHAR(6) -- (EindWeek -1 week) - 4 weken, of eigenlijk @EindWeek -4
DECLARE @EindWeekBlok5 VARCHAR(6)
DECLARE @StartWeekBlok4 VARCHAR(6) -- (EindWeek -2 weken) - 4 weken, of eigenlijk @EindWeek -5
DECLARE @EindWeekBlok4 VARCHAR(6)
DECLARE @StartWeekBlok3 VARCHAR(6) -- (EindWeek -3 weken) - 4 weken, of eigenlijk @EindWeek -6
DECLARE @EindWeekBlok3 VARCHAR(6)
DECLARE @StartWeekBlok2 VARCHAR(6) -- (EindWeek -4 weken) - 4 weken, of eigenlijk @EindWeek -7
DECLARE @EindWeekBlok2 VARCHAR(6)
DECLARE @StartWeekBlok1 VARCHAR(6) -- (EindWeek -5 weken) - 4 weken, of eigenlijk @EindWeek -8
DECLARE @EindWeekBlok1 VARCHAR(6)

Stap 2: Geef de blokken een waarde

SQL
SET @StartWeekBlok6 = (SELECT dbo.fnDateAddWeek(@EindWeek,-3,0))
SET @StartWeekBlok5 = (SELECT dbo.fnDateAddWeek(@EindWeek,-4,0))
SET @StartWeekBlok4 = (SELECT dbo.fnDateAddWeek(@EindWeek,-5,0))
SET @StartWeekBlok3 = (SELECT dbo.fnDateAddWeek(@EindWeek,-6,0))
SET @StartWeekBlok2 = (SELECT dbo.fnDateAddWeek(@EindWeek,-7,0))
SET @StartWeekBlok1 = (SELECT dbo.fnDateAddWeek(@EindWeek,-8,0))


SET @EindWeekBlok6 = (SELECT dbo.fnDateAddWeek(@EindWeek,0,0))
SET @EindWeekBlok5 = (SELECT dbo.fnDateAddWeek(@EindWeek,-1,0))
SET @EindWeekBlok4 = (SELECT dbo.fnDateAddWeek(@EindWeek,-2,0))
SET @EindWeekBlok3 = (SELECT dbo.fnDateAddWeek(@EindWeek,-3,0))
SET @EindWeekBlok2 = (SELECT dbo.fnDateAddWeek(@EindWeek,-4,0))
SET @EindWeekBlok1 = (SELECT dbo.fnDateAddWeek(@EindWeek,-5,0))

** Let op: ik gebruik een functie om de weken te berekenen. Daarvoor heb je de datumdimensie nodig. Met deze functie haal je handig een week in het verleden of de toekomst op, op basis van een opgegeven week. Je kunt de startweek ook meetellen in het aantal weken.

SQL
CREATE FUNCTION [dbo].[fnDateAddWeek]
(
@StartWeek INT,
@NrWeeks INT,
@IncludeStartWeek BIT=0 -- als je hier 1 zet telt hij een week minder terug of verder
)
-- [fnDateAddWeek]
-- Berekent een weeknummer dat voor of na de opgegeven week ligt obv de opgegeven weken
-- Bijv: het is nu week 50. Je wilt weten welke week 4 weken terug is, dit is 46
-- SELECT dbo.fnDateAddWeek (201550, -4,0)
-- SELECT dbo.fnDateAddWeek (201601, -4,0)


RETURNS INT
AS
BEGIN
DECLARE @Result INT


SET @Result = (SELECT DD.WeekNummer
FROM
(SELECT CONVERT(VARCHAR, DATEADD(WK, @NrWeeks
- (CASE WHEN @IncludeStartWeek=1 AND @NrWeeks < 0 THEN -1
WHEN @IncludeStartWeek=1 AND @NrWeeks > 0 THEN 1 ELSE -0 END)
, D.DatumWaarde),112) AS DatumID
FROM dbo.DimDatum AS D
WHERE DatumID = (SELECT MAX(DatumID) AS DatumID
FROM dbo.DimDatum
WHERE WeekNummer = @StartWeek
)
) AS D
JOIN DimDatum AS DD ON D.DatumID = DD.DatumID
)
RETURN @Result
END

Stap 3: Maak een CTE met de measures om te groeperen

SQL
;
WITH BS
AS
(SELECT DP.PeriodeNummer
, DP.Jaar
, RIGHT(DP.PeriodeNummer,2) AS PeriodeNaamKortGeenJaar
, ISNULL(SUM(KPI.AantalPlaatsing),0) AS AantalPlaatsing
, ISNULL(SUM(KPI.AantalVacatures),0) AS AantalVacatures
, ISNULL(SUM(KPI.AantalSollicitaties),0) AS AantalSollicitaties
FROM ps.FactPeriodeKPI AS KPI
JOIN dbo.DimPeriode AS DP ON KPI.PeriodeID = DP.PeriodeID
JOIN dbo.DimOrganisatie AS DO ON KPI.OrganisatieID = DO.OrganisatieID
JOIN dbo.DimBranch AS DB ON DO.BranchID = DB.BranchID
WHERE (@GeselecteerdeOrganisatieLevelID = 4
OR (@GeselecteerdeOrganisatieLevelID = 3 AND DB.ZoneID = @GeselecteerdeOrganisatieID)
OR (@GeselecteerdeOrganisatieLevelID = 2 AND DB.AreaID = @GeselecteerdeOrganisatieID)
OR (@GeselecteerdeOrganisatieLevelID = 1 AND DB.BranchID = @GeselecteerdeOrganisatieID)
)
AND DP.PeriodeGrootheidID = 1
AND DP.PeriodeNummer BETWEEN @EindWeekBlok1 AND @EindWeekBlok6
GROUP BY DP.PeriodeNummer,DP.Jaar


)

Stap 4: Maak een CTE die de groepen genereert

SQL
, WG -- WG: WeekGroepjes: genereer een nummer voor groepjes van 4 weken, eindigt
-- bij de laatste volledige week.
AS
(


SELECT 1 AS PeriodeNummerRolling4Weken
, @StartWeekBlok6 AS StartWeek
, @EindWeekBlok6 AS EindWeek
, @StartWeekBlok6 + ' t/m ' + @EindWeekBlok6 AS SparklineLabel
FROM dbo.DimDatum AS DD


UNION


SELECT 2 AS PeriodeNummerRolling4Weken
, @StartWeekBlok5 AS StartWeek
, @EindWeekBlok5 AS EindWeek
, @StartWeekBlok5 + ' t/m ' + @EindWeekBlok5 AS SparklineLabel


UNION


SELECT 3 AS PeriodeNummerRolling4Weken
, @StartWeekBlok4 AS StartWeek
, @EindWeekBlok4 AS EindWeek
, @StartWeekBlok4 + ' t/m ' + @EindWeekBlok4 AS SparklineLabel


UNION


SELECT 4 AS PeriodeNummerRolling4Weken
, @StartWeekBlok3 AS StartWeek
, @EindWeekBlok3 AS EindWeek
, @StartWeekBlok3 + ' t/m ' + @EindWeekBlok3 AS SparklineLabel


UNION


SELECT 5 AS PeriodeNummerRolling2Weken
, @StartWeekBlok2 AS StartWeek
, @EindWeekBlok2 AS EindWeek
, @StartWeekBlok2 + ' t/m ' + @EindWeekBlok2 AS SparklineLabel


UNION


SELECT 6 AS PeriodeNummerRolling1Weken
, @StartWeekBlok1 AS StartWeek
, @EindWeekBlok1 AS EindWeek
, @StartWeekBlok1 + ' t/m ' + @EindWeekBlok1 AS SparklineLabel
)

Stap 5: Combineer beide CTE’s

SQL
SELECT WG.PeriodeNummerRolling4Weken
, WG.StartWeek
, WG.EindWeek
, WG.SparklineLabel
, SUM(BS.AantalPlaatsing) AS AantalPlaatsing
, SUM(BS.AantalVacatures) AS AantalVacatures
, SUM(BS.AantalSollicitaties) AS AantalSollicitaties
FROM WG
JOIN BS ON BS.PeriodeNummer BETWEEN WG.StartWeek AND WG.EindWeek
GROUP BY WG.PeriodeNummerRolling4Weken ,
WG.StartWeek ,
WG.EindWeek ,
WG.SparklineLabel

Stap 6: het gecombineerde resultaat

SQL
DECLARE @EindWeek AS INT = (SELECT MIN(WeekNummer) FROM dbo.DimDatum WHERE DatumID = CONVERT(VARCHAR, DATEADD(dd,-7,GETDATE()),112) )


DECLARE @StartWeekBlok6 VARCHAR(6) -- (EindWeek -3 Weken) (dit is het laatste blok)
DECLARE @EindWeekBlok6 VARCHAR(6)
DECLARE @StartWeekBlok5 VARCHAR(6) -- (EindWeek -1 week) - 4 weken, of eigenlijk @EindWeek -4
DECLARE @EindWeekBlok5 VARCHAR(6)
DECLARE @StartWeekBlok4 VARCHAR(6) -- (EindWeek -2 weken) - 4 weken, of eigenlijk @EindWeek -5
DECLARE @EindWeekBlok4 VARCHAR(6)
DECLARE @StartWeekBlok3 VARCHAR(6) -- (EindWeek -3 weken) - 4 weken, of eigenlijk @EindWeek -6
DECLARE @EindWeekBlok3 VARCHAR(6)
DECLARE @StartWeekBlok2 VARCHAR(6) -- (EindWeek -4 weken) - 4 weken, of eigenlijk @EindWeek -7
DECLARE @EindWeekBlok2 VARCHAR(6)
DECLARE @StartWeekBlok1 VARCHAR(6) -- (EindWeek -5 weken) - 4 weken, of eigenlijk @EindWeek -8
DECLARE @EindWeekBlok1 VARCHAR(6)


SET @StartWeekBlok6 = (SELECT dbo.fnDateAddWeek(@EindWeek,-3,0))
SET @StartWeekBlok5 = (SELECT dbo.fnDateAddWeek(@EindWeek,-4,0))
SET @StartWeekBlok4 = (SELECT dbo.fnDateAddWeek(@EindWeek,-5,0))
SET @StartWeekBlok3 = (SELECT dbo.fnDateAddWeek(@EindWeek,-6,0))
SET @StartWeekBlok2 = (SELECT dbo.fnDateAddWeek(@EindWeek,-7,0))
SET @StartWeekBlok1 = (SELECT dbo.fnDateAddWeek(@EindWeek,-8,0))


SET @EindWeekBlok6 = (SELECT dbo.fnDateAddWeek(@EindWeek,0,0))
SET @EindWeekBlok5 = (SELECT dbo.fnDateAddWeek(@EindWeek,-1,0))
SET @EindWeekBlok4 = (SELECT dbo.fnDateAddWeek(@EindWeek,-2,0))
SET @EindWeekBlok3 = (SELECT dbo.fnDateAddWeek(@EindWeek,-3,0))
SET @EindWeekBlok2 = (SELECT dbo.fnDateAddWeek(@EindWeek,-4,0))
SET @EindWeekBlok1 = (SELECT dbo.fnDateAddWeek(@EindWeek,-5,0))


--SELECT @StartWeekBlok6,@StartWeekBlok5,@StartWeekBlok4,@StartWeekBlok3,@StartWeekBlok2,@StartWeekBlok1
--SELECT @EindWeekBlok6,@EindWeekBlok5,@EindWeekBlok4,@EindWeekBlok3,@EindWeekBlok2,@EindWeekBlok1


-- ** -----------------------------------------------------------------------------------------------------------
-- ** -----------------------------------------------------------------------------------------------------------
;
WITH BS
AS
(SELECT DP.PeriodeNummer
, DP.Jaar
, RIGHT(DP.PeriodeNummer,2) AS PeriodeNaamKortGeenJaar
, ISNULL(SUM(KPI.AantalPlaatsing),0) AS AantalPlaatsing
, ISNULL(SUM(KPI.AantalVacatures),0) AS AantalVacatures
, ISNULL(SUM(KPI.AantalSollicitaties),0) AS AantalSollicitaties
FROM ps.FactPeriodeKPI AS KPI
JOIN dbo.DimPeriode AS DP ON KPI.PeriodeID = DP.PeriodeID
JOIN dbo.DimOrganisatie AS DO ON KPI.OrganisatieID = DO.OrganisatieID
JOIN dbo.DimBranch AS DB ON DO.BranchID = DB.BranchID
WHERE (@GeselecteerdeOrganisatieLevelID = 4
OR (@GeselecteerdeOrganisatieLevelID = 3 AND DB.ZoneID = @GeselecteerdeOrganisatieID)
OR (@GeselecteerdeOrganisatieLevelID = 2 AND DB.AreaID = @GeselecteerdeOrganisatieID)
OR (@GeselecteerdeOrganisatieLevelID = 1 AND DB.BranchID = @GeselecteerdeOrganisatieID)
)
AND DP.PeriodeGrootheidID = 1
AND DP.PeriodeNummer BETWEEN @EindWeekBlok1 AND @EindWeekBlok6
GROUP BY DP.PeriodeNummer,DP.Jaar


)


, WG -- WG: WeekGroepjes: genereer een nummer voor groepjes van 4 weken, eindigt
-- bij de laatste volledige week.
AS
(


SELECT 1 AS PeriodeNummerRolling4Weken
, @StartWeekBlok6 AS StartWeek
, @EindWeekBlok6 AS EindWeek
, @StartWeekBlok6 + ' t/m ' + @EindWeekBlok6 AS SparklineLabel
FROM dbo.DimDatum AS DD


UNION


SELECT 2 AS PeriodeNummerRolling4Weken
, @StartWeekBlok5 AS StartWeek
, @EindWeekBlok5 AS EindWeek
, @StartWeekBlok5 + ' t/m ' + @EindWeekBlok5 AS SparklineLabel


UNION


SELECT 3 AS PeriodeNummerRolling4Weken
, @StartWeekBlok4 AS StartWeek
, @EindWeekBlok4 AS EindWeek
, @StartWeekBlok4 + ' t/m ' + @EindWeekBlok4 AS SparklineLabel


UNION


SELECT 4 AS PeriodeNummerRolling4Weken
, @StartWeekBlok3 AS StartWeek
, @EindWeekBlok3 AS EindWeek
, @StartWeekBlok3 + ' t/m ' + @EindWeekBlok3 AS SparklineLabel


UNION


SELECT 5 AS PeriodeNummerRolling2Weken
, @StartWeekBlok2 AS StartWeek
, @EindWeekBlok2 AS EindWeek
, @StartWeekBlok2 + ' t/m ' + @EindWeekBlok2 AS SparklineLabel


UNION


SELECT 6 AS PeriodeNummerRolling1Weken
, @StartWeekBlok1 AS StartWeek
, @EindWeekBlok1 AS EindWeek
, @StartWeekBlok1 + ' t/m ' + @EindWeekBlok1 AS SparklineLabel
)


SELECT WG.PeriodeNummerRolling4Weken
, WG.StartWeek
, WG.EindWeek
, WG.SparklineLabel
, SUM(BS.AantalPlaatsing) AS AantalPlaatsing
, SUM(BS.AantalVacatures) AS AantalVacatures
, SUM(BS.AantalSollicitaties) AS AantalSollicitaties
FROM WG
JOIN BS ON BS.PeriodeNummer BETWEEN WG.StartWeek AND WG.EindWeek
GROUP BY WG.PeriodeNummerRolling4Weken ,
WG.StartWeek ,
WG.EindWeek ,
WG.SparklineLabel

Je kunt deze query als procedure opslaan en uitvoeren… Het resultaat ziet er ongeveer zo uit (dummydata):

Sql_4Week_Moving_total_SPCD

Deze data gebruik ik voor mijn sparkline, die de trend van de laatste weken laat zien:

Sql_4Week_Moving_total_Sparkline

Veelgestelde vragen

Hoe bereken ik een voortschrijdend totaal over 4 weken in SQL Server?

Gebruik een datumdimensie. Zoek de laatste volledige week, declareer blokken van vier weken die elk een week terugschuiven en join die blokken aan een CTE met de measures. Groepeer daarna per blok.

Wat is een voortschrijdend totaal over 4 weken?

Het is een som of gemiddelde over een aantal weken. Het venster schuift steeds een week terug. Blok 1 neemt week 37-40, blok 2 neemt week 36-39, enzovoort.

Hoe vind ik de laatste volledige week in T-SQL?

Haal MIN(WeekNummer) op uit dbo.DimDatum voor de DatumID van vandaag min zeven dagen, omgezet met stijl 112. De weken schuiven dan vanzelf mee, zonder dat er iets hardcoded is.

Wat doet de functie fnDateAddWeek?

Hij geeft het weeknummer dat een opgegeven aantal weken voor of na een startweek ligt, op basis van de datumdimensie. Week 50 min vier weken geeft week 46. Je kunt de startweek ook meetellen.

Waarom een voortschrijdend totaal over 4 weken in een dashboard?

Het voedt een trendlijn of sparkline voor belangrijke bedrijfsgegevens. Het dashboard toont data tot en met de laatste volledige week, dus de weken moeten in de tijd meeschuiven zonder hardcoded waarden.

Reacties

Je e-mailadres wordt niet getoond. Een foto komt van Gravatar, als je die hebt.

  1. Doellijn/Stripline in Reporting Services

    SQL Server29 aug 2012 · 4,9K keer gelezen
    4,9K

  2. Running Totals / Cumulatieven berekenen

    SQL Server24 mei 2011 · 5,3K keer gelezen
    5,3K

  3. Parameter Security bij Export Excel

    SQL Server17 sep 2008 · 3,3K keer gelezen
    3,3K
(3 rows affected)SQL Server