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 -4DECLARE @EindWeekBlok5 VARCHAR(6)DECLARE @StartWeekBlok4 VARCHAR(6) -- (EindWeek -2 weken) - 4 weken, of eigenlijk @EindWeek -5DECLARE @EindWeekBlok4 VARCHAR(6)DECLARE @StartWeekBlok3 VARCHAR(6) -- (EindWeek -3 weken) - 4 weken, of eigenlijk @EindWeek -6DECLARE @EindWeekBlok3 VARCHAR(6)DECLARE @StartWeekBlok2 VARCHAR(6) -- (EindWeek -4 weken) - 4 weken, of eigenlijk @EindWeek -7DECLARE @EindWeekBlok2 VARCHAR(6)DECLARE @StartWeekBlok1 VARCHAR(6) -- (EindWeek -5 weken) - 4 weken, of eigenlijk @EindWeek -8DECLARE @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 INTASBEGINDECLARE @Result INT
SET @Result = (SELECT DD.WeekNummerFROM(SELECT CONVERT(VARCHAR, DATEADD(WK, @NrWeeks- (CASE WHEN @IncludeStartWeek=1 AND @NrWeeks < 0 THEN -1WHEN @IncludeStartWeek=1 AND @NrWeeks > 0 THEN 1 ELSE -0 END), D.DatumWaarde),112) AS DatumIDFROM dbo.DimDatum AS DWHERE DatumID = (SELECT MAX(DatumID) AS DatumIDFROM dbo.DimDatumWHERE WeekNummer = @StartWeek)) AS DJOIN DimDatum AS DD ON D.DatumID = DD.DatumID)RETURN @ResultENDStep 3: Setup CTE with measures to group
;WITH BSAS(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 AantalSollicitatiesFROM ps.FactPeriodeKPI AS KPIJOIN dbo.DimPeriode AS DP ON KPI.PeriodeID = DP.PeriodeIDJOIN dbo.DimOrganisatie AS DO ON KPI.OrganisatieID = DO.OrganisatieIDJOIN dbo.DimBranch AS DB ON DO.BranchID = DB.BranchIDWHERE (@GeselecteerdeOrganisatieLevelID = 4OR (@GeselecteerdeOrganisatieLevelID = 3 AND DB.ZoneID = @GeselecteerdeOrganisatieID)OR (@GeselecteerdeOrganisatieLevelID = 2 AND DB.AreaID = @GeselecteerdeOrganisatieID)OR (@GeselecteerdeOrganisatieLevelID = 1 AND DB.BranchID = @GeselecteerdeOrganisatieID))AND DP.PeriodeGrootheidID = 1AND DP.PeriodeNummer BETWEEN @EindWeekBlok1 AND @EindWeekBlok6GROUP 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 SparklineLabelFROM 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 AantalSollicitatiesFROM WGJOIN BS ON BS.PeriodeNummer BETWEEN WG.StartWeek AND WG.EindWeekGROUP BY WG.PeriodeNummerRolling4Weken ,WG.StartWeek ,WG.EindWeek ,WG.SparklineLabelStep 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 -4DECLARE @EindWeekBlok5 VARCHAR(6)DECLARE @StartWeekBlok4 VARCHAR(6) -- (EindWeek -2 weken) - 4 weken, of eigenlijk @EindWeek -5DECLARE @EindWeekBlok4 VARCHAR(6)DECLARE @StartWeekBlok3 VARCHAR(6) -- (EindWeek -3 weken) - 4 weken, of eigenlijk @EindWeek -6DECLARE @EindWeekBlok3 VARCHAR(6)DECLARE @StartWeekBlok2 VARCHAR(6) -- (EindWeek -4 weken) - 4 weken, of eigenlijk @EindWeek -7DECLARE @EindWeekBlok2 VARCHAR(6)DECLARE @StartWeekBlok1 VARCHAR(6) -- (EindWeek -5 weken) - 4 weken, of eigenlijk @EindWeek -8DECLARE @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 BSAS(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 AantalSollicitatiesFROM ps.FactPeriodeKPI AS KPIJOIN dbo.DimPeriode AS DP ON KPI.PeriodeID = DP.PeriodeIDJOIN dbo.DimOrganisatie AS DO ON KPI.OrganisatieID = DO.OrganisatieIDJOIN dbo.DimBranch AS DB ON DO.BranchID = DB.BranchIDWHERE (@GeselecteerdeOrganisatieLevelID = 4OR (@GeselecteerdeOrganisatieLevelID = 3 AND DB.ZoneID = @GeselecteerdeOrganisatieID)OR (@GeselecteerdeOrganisatieLevelID = 2 AND DB.AreaID = @GeselecteerdeOrganisatieID)OR (@GeselecteerdeOrganisatieLevelID = 1 AND DB.BranchID = @GeselecteerdeOrganisatieID))AND DP.PeriodeGrootheidID = 1AND DP.PeriodeNummer BETWEEN @EindWeekBlok1 AND @EindWeekBlok6GROUP 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 SparklineLabelFROM 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 AantalSollicitatiesFROM WGJOIN BS ON BS.PeriodeNummer BETWEEN WG.StartWeek AND WG.EindWeekGROUP BY WG.PeriodeNummerRolling4Weken ,WG.StartWeek ,WG.EindWeek ,WG.SparklineLabelYou 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:

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
Related blogs
Target line/StripLine in Reporting Services
Calculating running totals
Parameter security when exporting to Excel