SQLBlog Nederland Business Intelligence & Datawarehousing
Ik had vorige week een leuke uitdaging. In de KPI tabel die ik gebruik voor bepaalde rapportages zijn enkel de normale werkdagen uitgerekend, maandag t/m vrijdag. Feestdagen komen ook voor. De weekenden zijn hier bewust uitgelaten ivm FTE tellingen die anders niet lekker lopen.
Bij het uitrekenen van KPI’s kwam ik in de problemen omdat bepaalde KPI’s waarden vanuit de bronsystemen op de weekenden werden ingevoerd. De keus was nu, op welke dag gaan we deze waarden plaatsen? Mijn eerste idee was om ze gewoon op de vrijdag ervoor te zetten. Dit is niet altijd juist omdat de vrijdag ervoor ook in een andere maand/jaar kan vallen 🙂 Mijn rapportage wordt op maand gedraaid dus dit is ook geen goede oplossing.
Voor dit probleem kon ik voordat ik de data in de fact ging laden een ingewikkelde SQL maken om de juiste dag te vinden waarop de waarde gezet moest worden. Wat een betere oplossing zou zijn was om de juiste datum al vooraf te bepalen en op te slaan in de Datumtabel. Iedere datum krijgt dan een “BoekDatumID”. Â De defintie van dit veld is dat het de dichtsbijzijnde werkdag moest zijn die in dezelfde maand/jaar viel en geen feestdag moet zijn.
Nu ben je natuurlijk benieuwd hoe de SQL eruit zien om de juiste werkdag te vinden. Deze is betrekkelijk eenvoudig maar je moet er wel even opkomen!
Uitleg: er vindt een explosie plaatst op datum waarbij per datum 10 dagen ervoor en 10 dagen erna worden geselecteerd die in dezelfde maand/jaar vallen. Vervolgens wordt de dichtstbijzijnde dag geselecteerde met RowNumber = 1.
Easy?!?! Dit resultaat gebruik je om je datumtabel bij te werken. Zie bijlage voor de SQL
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