Home / Reporting Services / T-TSQL – Create 4 week Moving Total

T-TSQL – Create 4 week Moving Total

Reporting Services, T-SQL 5,403 Views

This article will offer you a way to calculate a 4 week moving total in SQL using SQL Server. There are more ways to Rome but this is a way you can use to calculate it using a date dimension.

What do I mean by a 4 week moving total? It’s a sum or average number, calculated over a number of weeks. The number moves one week back, getting the next four weeks. In the example below, we take week 37-40, we call this block 1. Then we move one week back in the past and take week 36-39, we call this block 2. Etc.

Sql_4Week_Moving_total

Why is this useful? We can use this moving 4 week in a dashboard to show a trendline or sparkline for important business data.

The first step is determine how many “groups” you need with 4 weeks of data. In my example, I have four groups of weeks, block 1 – block 4. For the SQL example, I use six blocks. The weeks must move dynamic within time without hardcoded stuff inside because the dashboard shows data till last complete week.

Step 1: Determine latest complete week

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

Step 2: Declare blocks of weeks

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)

Step 2: Assign values to blocks

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

** Note that I use a function to calculate the weeks, you need the data dimension. This function is handy to get a week in the past or future, based on an input week. You can also include the startweek as a count of weeks.

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

Step 3: Setup CTE with measures to group

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

Step 4: Setup CTE to genereate groups

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

Step 5: Combine both CTE’s

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

Step 6: combined result

 

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

You can save this query as a procedure and run it… The result will look something like this (dummy data):

Sql_4Week_Moving_total_SPCD

I’m using this data for my sparkline to show the trend from the latest weeks:

Sql_4Week_Moving_total_Sparkline

Tags

Check Also

Book Review: Unlocking the Power of DAX: A Deep Dive into Marco Russo’s Definitive Guide

The Definitive Guide to DAX: Business intelligence for Microsoft Power BI, SQL Server Analysis Services, …

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *

© Copyright 2024, All Rights Reserved SQLBlog.nl