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

SQL
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

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)

Step 2: Assign values to blocks

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

** 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.

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

Step 3: Setup CTE with measures to group

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
)

Step 4: Setup CTE to genereate groups

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
)

Step 5: Combine both 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

Step 6: combined result

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

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

Frequently asked questions

How do I calculate a 4 week moving total in SQL Server?

Use a date dimension. Find the latest complete week, declare blocks of four weeks that each move one week back, and join those blocks to a CTE with the measures. Then group by block.

What is a 4 week moving total?

It is a sum or average over a number of weeks. The window moves one week back each time. Block 1 takes weeks 37-40, block 2 takes weeks 36-39, and so on.

How do I find the latest complete week in T-SQL?

Select MIN(WeekNummer) from dbo.DimDatum where DatumID equals today minus seven days, converted with style 112. The weeks then move dynamically, with nothing hardcoded.

What does the fnDateAddWeek function do?

It returns the week number a given number of weeks before or after a start week, based on the date dimension. Week 50 minus four weeks gives week 46. You can also count the start week.

Why use a moving 4 week total in a dashboard?

It feeds a trendline or sparkline for important business data. The dashboard shows data up to the last complete week, so the weeks must move over time without hardcoded values.

Comments

Your email address is not shown. A photo comes from Gravatar, if you have one.