Last week I had a nice challenge. In the KPI table I use for certain reports, only the normal working days are calculated, Monday to Friday. Public holidays are in there too. The weekends are left out on purpose because of FTE counts, which otherwise do not work out right.
When I calculated the KPIs I ran into trouble, because the source systems entered values for certain KPIs on weekends. The question was: on which day do we put these values? My first idea was to simply put them on the Friday before. That is not always correct, because the Friday before can also fall in another month/year :-) My report runs per month, so this is not a good solution either.
For this problem I could write complicated SQL to find the right day for the value, before I loaded the data into the fact. A better solution would be to work out the correct date in advance and store it in the date table. Every date then gets a “BoekDatumID”. The definition of this field is that it must be the nearest working day that falls in the same month/year and is not a public holiday.
Now of course you want to know what the SQL looks like to find the right working day. It is fairly simple, but you have to think of it first!
Explanation: we explode the dates. For each date we select the 10 days before and the 10 days after that fall in the same month/year. Then we select the nearest day with RowNumber = 1.
Easy?!?! You use this result to update your date table. See the attachment for the SQL
Frequently asked questions
How do I find the nearest working day for a date in SQL Server?
For each date, select the 10 days before and the 10 days after that fall in the same month and year. From those, pick the nearest working day with RowNumber = 1.
Why not simply move weekend values to the Friday before?
The Friday before can fall in another month or another year. A report that runs per month then gives a wrong result.
What is a BoekDatumID in a date table?
It is a field in the date table with the nearest working day for each date. That day falls in the same month and year and is not a public holiday. That way you do not need complicated SQL up front.
Why store the correct date in the date table instead of during the load?
Complicated SQL just before loading the fact table also works. It is better to work out the correct date in advance and store it in the date table. You update the date table with the result.
Why are weekends missing from a KPI table?
The weekends are left out on purpose because of FTE counts, which otherwise do not work out right. Only Monday to Friday are in it. So values from source systems on a weekend must move to a working day.
1 comment
SELECT *
FROM
(SELECT DD.DatumId, DD.DatumWaarde, BD.DatumID AS BoekDatumID
,ROW_NUMBER() OVER(PARTITION BY DD.DatumId
ORDER BY
(CASE WHEN DD.WeekNummer = BD.WeekNummer THEN 0 ELSE 1 END) ASC,
ABS(DATEDIFF(dd,DD.DatumWaarde, BD.DatumWaarde)) ASC,
BD.DagNummerVanWeek ASC
) AS RowID
FROM dbo.DimDatum AS DD
-- BD: Boek Datum
INNER JOIN dbo.DimDatum AS BD
ON DD.Jaar = BD.Jaar
AND DD.MaandNummerVanJaar = BD.MaandNummerVanJaar
WHERE ABS(DATEDIFF(dd,DD.DatumWaarde, BD.DatumWaarde))<=10
AND (DD.IsWerkDag=0 OR DD.IsFeestDag_NL=1)
AND BD.IsWerkDag=1
AND BD.IsFeestDag_NL=0
) AS X
WHERE X.RowID=1
Related blogs
Dutch public holidays in SQL Server
After update trigger
Centralizing KPIs in your data warehouse