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.
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):
I’m using this data for my sparkline to show the trend from the latest weeks: