• Increase font size
  • Default font size
  • Decrease font size
Home T-SQL Hoe maak ik een datumdimensie?

Hoe maak ik een datumdimensie?

dinsdag, 24 februari 2009 13:40 Ronald Kraijesteijn
E-mail Afdrukken
(4 stemmen)

coldfusion_1_zoom_128Datumdimensies zijn enorm handig. Door gebruik te maken van een datumdimensie kun je voor een datum bepalen in welke week deze datum valt, welk jaar, welke maand, of het een feestdag is, etc. Hieronder het script.

Let op; dit is een voorbeelddimensie. Je kunt hem uiteraard zelf verfraaien met velden die voor jou belangrijk zijn!

CREATE PROCEDURE [dbo].[Generate_REF_Datum] (@dStartdate AS DATETIME, @dEnddate AS DATETIME)
-- EXAMPLE:
-- TRUNCATE TABLE REF_DATUM
-- EXEC Generate_REF_Datum '20090101', '20110101'
AS
BEGIN
 
DECLARE    @iDatumID INT                        --    [DatumId] [int] NOT NULL,    
DECLARE    @vcDatumNaam VARCHAR(64)            --    [DatumNaam] [varchar](64) NULL,    
DECLARE    @vcDagNaam VARCHAR(64)                --    [DagNaam] [varchar](64) NULL,    
DECLARE    @vcMaandNaam VARCHAR(64)            --    [MaandNaam] [varchar](64) NULL,    
DECLARE    @vcMaandCode VARCHAR(4)                --    [MaandCode] [varchar](4) NULL,    
DECLARE    @vcKwartaalNaam VARCHAR(64)            --    [KwartaalNaam] [varchar](64) NULL,    
DECLARE    @vcKwartaalCode VARCHAR(4)            --    [KwartaalCode] [varchar](4) NULL,    
DECLARE    @iDagNummerVanWeek INT                --    [DagNummerVanWeek] [int] NULL,    
DECLARE    @iDagNummerVanMaand INT                --    [DagNummerVanMaand] [int] NULL,    
DECLARE    @iDagNummerVanKwartaal INT            --    [DagNummerVanKwartaal] [int] NULL,    
DECLARE    @iDagNummerVanJaar INT                --    [DagNummerVanJaar] [int] NULL,    
DECLARE    @iISOWeeknr INT                     --    [WeekNummerVanJaar] [int] NULL,    
DECLARE    @iMaandNummer INT                    --    [MaandNummerVanJaar] [int] NULL,    
DECLARE    @iKwartaalNummer INT                --    [KwartaalNummerVanJaar] [int] NULL,    
DECLARE    @iJaar INT                            --    [Jaar] [int] NULL,    
DECLARE    @iWeekNummer INT                    --    [WeekNummer] [int] NULL,    
DECLARE    @vcWeekNaam VARCHAR(64)                --    [WeekNaam] [varchar](64) NULL,    
DECLARE    @iJaarVanWeekNummer INT                --    [JaarVanWeekNummer] [int] NULL,    
DECLARE    @iIsLaatsteDagVanMaand INT            --    [IsLaatsteDagVanMaand] [bit] NULL,    
DECLARE    @iIsLaatsteDagVanKwartaal INT        --    [IsLaatsteDagVanKwartaal] [bit] NULL,    
DECLARE    @iIsLaatsteDagVanJaar INT            --    [IsLaatsteDagVanJaar] [bit] NULL,    
DECLARE    @iIsWerkDag INT                        --    [IsWerkDag] [bit] NULL,    
DECLARE    @iVerloning INT                        --    [IsVerloningDag] [bit] NULL,    
DECLARE    @iFeestdag INT                        --    [IsFeestDag_NL] [bit] NULL,    
 
DECLARE    @iPeriode444Nr INT                    --    [Periode444Nummer] [int] NULL,    
DECLARE    @vcPeriode444Naam VARCHAR(64)        --    [Periode444Naam] [varchar](64) NULL,    
DECLARE    @iPeriode445Nr INT                    --    [Periode445Nummer] [int] NULL,    
DECLARE    @vcPeriode445Naam VARCHAR(64)        --    [Periode445Naam] [varchar](64) NULL,    
DECLARE    @iKwartaal445Nummer INT                --    [Kwartaal445Nummer] [int] NULL,    
DECLARE    @iKwartaal445Code VARCHAR(4)        --    [Kwartaal445Code] [varchar](4) NULL,    
DECLARE    @iDataOK INT                        --    [IsDataCorrectGeladen] [bit] NULL,    
DECLARE    @iIsSelecteerbaar INT                --    [IsSelecteerbaar] [bit] NULL,    
 
 
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 
 -- IMPORTANT: changing this value will have impact on 
 -- results of functions and deliver wrong results.
 SET DATEFIRST 1
 
 -- loop through date range between StartDate and EndDate
 -- StartDate is shifted upwards with one day after each itteration.
 WHILE @dStartdate <= @dEnddate
 BEGIN
 -- Setting the values of one day
 -- direct te bepalen variabelen
 SET @iDatumID                = (YEAR(@dStartdate)*10000)+(MONTH(@dStartdate)*100)+(DAY(@dStartdate))
 SET @vcDagNaam                = DATENAME(weekday, @dStartDate) 
 SET @iDagNummerVanWeek        = DATEPART(dw,@dStartdate) 
 SET @iMaandnummer            = DATEPART(M,@dStartdate)
 SET @vcMaandNaam            = DATENAME(MONTH,@dStartdate)
 SET @iDagNummerVanMaand        = DATEPART(DD,@dStartdate) 
 SET @iKwartaalNummer        = DATEPART(QQ,@dStartdate) 
 SET @vcKwartaalCode            = 'Q' + CAST(@iKwartaalNummer AS VARCHAR(2))
 SET @iJaar                    = DATEPART(YYYY,@dStartdate)
 SET @iDagNummerVanJaar        = DATEPART(dy,@dStartdate) 
 
 -- variabelen afhankelijk van andere (vorige) variabelen
 SET @iIsWerkDag                = CASE WHEN @iDagNummerVanWeek NOT IN (6,7) THEN 1 ELSE 0 END
 SET @vcDatumNaam            = @vcDagNaam + ' ' + CAST(@iDagNummerVanMaand AS VARCHAR(2)) + ' ' + @vcMaandNaam + ' ' +


CAST(@iJaar AS VARCHAR(4))
 SET @iISOWeeknr                = dbo.Get_ISOWeekNo(@dStartdate)
 SET @vcWeekNaam                = 'Week ' + CAST(@iISOWeeknr AS VARCHAR(2))
 SET @vcKwartaalNaam            = 'Kwartaal ' + CAST(@iKwartaalNummer AS VARCHAR(2))
 
 -- set initiele waarden bij variablen die later gecorriceerd worden.
 SET @iVerloning                = 0            -- Elke dinsdag behalve bij een vrije dag.
 SET @iPeriode444Nr            = 1            --Zie Update
 SET @iPeriode445Nr            = 1            --Zie Update
 SET @vcPeriode445Naam        = 'Dummy'    --Zie Update
 SET @vcMaandCode            = 'Dummy'    --Zie Update
 SET @iJaarVanWeekNummer        = @iJaar    -- Corrected later...
 SET @iFeestdag                = 0
 
 
 --DagNummerVanKwartaal 
 SET @iDagNummerVanKwartaal = 
 CASE 
 WHEN @iMaandnummer BETWEEN 1 AND 3 THEN DATEDIFF(dd, CAST(@iJaar AS VARCHAR(4)) + '0101', @dStartdate)+1
 WHEN @iMaandnummer BETWEEN 4 AND 6 THEN DATEDIFF(dd, CAST(@iJaar AS VARCHAR(4)) + '0401', @dStartdate)+1
 WHEN @iMaandnummer BETWEEN 7 AND 9 THEN DATEDIFF(dd, CAST(@iJaar AS VARCHAR(4)) + '0701', @dStartdate)+1
 WHEN @iMaandnummer BETWEEN 10 AND 12 THEN DATEDIFF(dd, CAST(@iJaar AS VARCHAR(4)) + '1001', @dStartdate)+1
 END
 
 -- JaarVanWeekNummer
 -- De laatste dagen van een jaar kunnen in week vallen van volgend jaar.
 -- Neem het volgende jaar als weeknr=1 en maandnr=12.
 -- functie: dbo.usfIsoWeekNrOfDate zou ook uitkomst kunnen bieden
 -- huidige oplossing is het gemakkelijkst
 IF (@iISOWeeknr=1) AND (@iMaandnummer=12)
 SET @iJaarVanWeekNummer = @iJaarVanWeekNummer + 1
 
 IF (@iISOWeeknr>50) AND (@iMaandnummer=1)
 SET @iJaarVanWeekNummer = @iJaarVanWeekNummer - 1
 
 --Weeknummer (incl. JaarNummer)
 SET @iWeekNummer = (@iJaarVanWeekNummer * 100) + @iISOWeeknr
 
 
 --IsLaatsteDagVanMaand
 IF DATEPART(DD,@dStartdate + 1)  = 1
 SET @iIsLaatsteDagVanMaand = 1
 ELSE 
 SET @iIsLaatsteDagVanMaand = 0 
 
 --IsLaatsteDagVanKwartaal
 IF (@iIsLaatsteDagVanMaand = 1) AND (@iMaandnummer IN (3,6,9,12))
 SET @iIsLaatsteDagVanKwartaal = 1
 ELSE 
 SET @iIsLaatsteDagVanKwartaal = 0 
 
 --IsLaatsteDagVanJaar
 IF (@iIsLaatsteDagVanKwartaal = 1) AND (@iMaandnummer = 12)
 SET @iIsLaatsteDagVanJaar = 1
 ELSE 
 SET @iIsLaatsteDagVanJaar = 0 
 
 
 -- Kwartaal445Nummer
 IF @iISOWeeknr BETWEEN 1 AND 13
 SET @iKwartaal445Nummer = 1
 ELSE IF @iISOWeeknr BETWEEN 14 AND 26
 SET @iKwartaal445Nummer = 2
 ELSE IF @iISOWeeknr BETWEEN 27 AND 39
 SET @iKwartaal445Nummer = 3
 ELSE IF @iISOWeeknr BETWEEN 40 AND 53
 SET @iKwartaal445Nummer = 4
 ELSE
 SET @iKwartaal445Nummer = 0        
 
 --Kwartaal445Code
 SET @iKwartaal445Code = 'Q' + CAST(@iKwartaal445Nummer AS VARCHAR(2))
 
 
 --Inserting the values 
 INSERT INTO REF_Datum(
 DatumId,
 DatumWaarde,
 DatumNaam,
 DagNaam, 
 MaandNaam,
 KwartaalNaam,
 KwartaalCode,
 DagNummerVanWeek,
 DagNummerVanMaand,
 DagNummerVanKwartaal,
 DagNummerVanJaar,
 WeekNummerVanJaar ,
 MaandNummerVanJaar, 
 KwartaalNummerVanJaar,
 Jaar,
 WeekNummer,
 WeekNaam,
 JaarVanWeekNummer,
 IsLaatsteDagVanMaand,
 IsLaatsteDagVanKwartaal,
 IsLaatsteDagVanJaar,
 IsWerkDag,
 IsVerloningDag,
 IsFeestDag_NL,
 Periode444Nummer, 
 Periode445Nummer,
 Periode445Naam,
 Kwartaal445Nummer,
 Kwartaal445Code,
 IsSelecteerbaar
 )
 VALUES (
 @iDatumID,
 @dStartdate,
 @vcDatumNaam,
 @vcDagNaam,
 @vcMaandNaam,
 @vcKwartaalNaam,
 @vcKwartaalCode,
 @iDagNummerVanWeek,
 @iDagNummerVanMaand,
 @iDagNummerVanKwartaal,
 @iDagNummerVanJaar,
 @iISOWeeknr,
 @iMaandNummer, 
 @iKwartaalNummer,
 @iJaar,
 @iWeekNummer,
 @vcWeekNaam,
 @iJaarVanWeekNummer,
 @iIsLaatsteDagVanMaand,
 @iIsLaatsteDagVanKwartaal,
 @iIsLaatsteDagVanJaar,
 @iIsWerkDag,
 @iVerloning,
 @iFeestdag,
 @iPeriode444Nr, 
 @iPeriode445Nr,
 @vcPeriode445Naam,
 @iKwartaal445Nummer,
 @iKwartaal445Code,
 0
 )
 
 SET @dStartdate = @dStartdate + 1
 END
 
 
 UPDATE D
 SET 
 ---------------------------------------------------------------------------------
 --Omzetten van dagen in het Engels naar het Nederlands!!!!!!
 ---------------------------------------------------------------------------------
 MaandNaam =
 CASE MaandNaam
 WHEN 'January'    THEN 'Januari'
 WHEN 'February'    THEN 'Februari'
 WHEN 'March'    THEN 'Maart'
 WHEN 'May'        THEN 'Mei'
 WHEN 'June'        THEN 'Juni'
 WHEN 'July'        THEN 'Juli'
 WHEN 'August'    THEN 'Augustus'
 ELSE MaandNaam
 END,
 
 DagNaam =
 CASE DagNaam
 WHEN 'Sunday'        THEN 'Zondag'
 WHEN 'Monday'        THEN 'Maandag'
 WHEN 'Tuesday'        THEN 'Dinsdag'
 WHEN 'Wednesday'    THEN 'Woensdag'
 WHEN 'Thursday'        THEN 'Donderdag'
 WHEN 'Friday'        THEN 'Vrijdag'
 WHEN 'Saturday'        THEN 'Zaterdag'
 ELSE DagNaam
 END,
 
 MaandCode =
 CASE MaandNummerVanJaar
 WHEN  1 THEN 'Jan'
 WHEN  2 THEN 'Feb'
 WHEN  3 THEN 'Mrt'
 WHEN  4 THEN 'Apl'
 WHEN  5 THEN 'Mei'
 WHEN  6 THEN 'Jun'
 WHEN  7 THEN 'Jul'
 WHEN  8 THEN 'Aug'
 WHEN  9 THEN 'Spt'
 WHEN 10 THEN 'Okt'
 WHEN 11 THEN 'Nov'
 WHEN 12 THEN 'Dec'
 ELSE ''
 END,
 
 ---------------------------------------------------------------------------------
 -- periode444 en periode445
 ---------------------------------------------------------------------------------
 Periode444Nummer = 
 CASE 
 WHEN WeekNummerVanJaar BETWEEN  1 AND  4 THEN  1
 WHEN WeekNummerVanJaar BETWEEN  5 AND  8 THEN  2
 WHEN WeekNummerVanJaar BETWEEN  9 AND 12 THEN  3
 WHEN WeekNummerVanJaar BETWEEN 13 AND 16 THEN  4
 WHEN WeekNummerVanJaar BETWEEN 17 AND 20 THEN  5
 WHEN WeekNummerVanJaar BETWEEN 21 AND 24 THEN  6
 WHEN WeekNummerVanJaar BETWEEN 25 AND 28 THEN  7
 WHEN WeekNummerVanJaar BETWEEN 29 AND 32 THEN  8
 WHEN WeekNummerVanJaar BETWEEN 33 AND 36 THEN  9
 WHEN WeekNummerVanJaar BETWEEN 37 AND 40 THEN 10
 WHEN WeekNummerVanJaar BETWEEN 41 AND 44 THEN 11
 WHEN WeekNummerVanJaar BETWEEN 45 AND 48 THEN 12
 WHEN WeekNummerVanJaar BETWEEN 49 AND 53 THEN 13
 END,
 
 Periode444Naam = 
 CASE 
 WHEN WeekNummerVanJaar BETWEEN 1  AND  4 THEN 'Week 1 t/m 4'
 WHEN WeekNummerVanJaar BETWEEN 5  AND  8 THEN 'Week 5 t/m 8'
 WHEN WeekNummerVanJaar BETWEEN 9  AND 12 THEN 'Week 9 t/m 12'
 WHEN WeekNummerVanJaar BETWEEN 13 AND 16 THEN 'Week 13 t/m 16'
 WHEN WeekNummerVanJaar BETWEEN 17 AND 20 THEN 'Week 17 t/m 20'
 WHEN WeekNummerVanJaar BETWEEN 21 AND 24 THEN 'Week 21 t/m 24'
 WHEN WeekNummerVanJaar BETWEEN 25 AND 28 THEN 'Week 25 t/m 28'
 WHEN WeekNummerVanJaar BETWEEN 29 AND 32 THEN 'Week 29 t/m 32'
 WHEN WeekNummerVanJaar BETWEEN 33 AND 36 THEN 'Week 33 t/m 36'
 WHEN WeekNummerVanJaar BETWEEN 37 AND 40 THEN 'Week 37 t/m 40'
 WHEN WeekNummerVanJaar BETWEEN 41 AND 44 THEN 'Week 41 t/m 44'
 WHEN WeekNummerVanJaar BETWEEN 45 AND 48 THEN 'Week 45 t/m 48'
 WHEN WeekNummerVanJaar BETWEEN 49 AND 53 THEN 'Week 49 t/m 52'
 END,
 
 Periode445Nummer = 
 CASE 
 WHEN WeekNummerVanJaar BETWEEN  1 AND  4 THEN  1    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN  5 AND  8 THEN  2    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN  9 AND 13 THEN  3    -- 5  weeks
 WHEN WeekNummerVanJaar BETWEEN 14 AND 17 THEN  4    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN 18 AND 21 THEN  5    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN 22 AND 26 THEN  6    -- 5  weeks
 WHEN WeekNummerVanJaar BETWEEN 27 AND 30 THEN  7    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN 31 AND 34 THEN  8    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN 35 AND 39 THEN  9    -- 5  weeks
 WHEN WeekNummerVanJaar BETWEEN 40 AND 43 THEN 10    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN 44 AND 47 THEN 11    --  4 weeks
 WHEN WeekNummerVanJaar BETWEEN 48 AND 53 THEN 12    -- 5  weeks, potentially 6 weeks if year contains 53 weeks.
 END,
 
 Periode445Naam = 
 CASE 
 WHEN WeekNummerVanJaar BETWEEN  1 AND  4 THEN 'Januari'
 WHEN WeekNummerVanJaar BETWEEN  5 AND  8 THEN 'Februari'
 WHEN WeekNummerVanJaar BETWEEN  9 AND 13 THEN 'Maart'
 WHEN WeekNummerVanJaar BETWEEN 14 AND 17 THEN 'April'
 WHEN WeekNummerVanJaar BETWEEN 18 AND 21 THEN 'Mei'
 WHEN WeekNummerVanJaar BETWEEN 22 AND 26 THEN 'Juni'
 WHEN WeekNummerVanJaar BETWEEN 27 AND 30 THEN 'Juli'
 WHEN WeekNummerVanJaar BETWEEN 31 AND 34 THEN 'Augustus'
 WHEN WeekNummerVanJaar BETWEEN 35 AND 39 THEN 'September'
 WHEN WeekNummerVanJaar BETWEEN 40 AND 43 THEN 'Oktober'
 WHEN WeekNummerVanJaar BETWEEN 44 AND 47 THEN 'November'
 WHEN WeekNummerVanJaar BETWEEN 48 AND 53 THEN 'December'
 END
 FROM REF_Datum AS D
 
 ---------------------------------------------------------------------------------
 -- Feestdagen
 -- Nieuwjaarsdag
 ---------------------------------------------------------------------------------
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE MaandNummerVanJaar = 1 AND DagNummerVanMaand = 1
 
 --Goeie Vrijdag
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE DatumWaarde = (DWH_REF.dbo.Get_EasterSunday(Jaar) - 2)
 
 --Paas Zondag
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE DatumWaarde = (DWH_REF.dbo.Get_EasterSunday(Jaar))
 
 --Paas Maandag
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE DatumWaarde = (DWH_REF.dbo.Get_EasterSunday(Jaar)+1)
 
 --Koninginnedag
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE MaandNummerVanJaar = 4 AND DagNummerVanMaand = 30
 
 --Hemelvaart
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE DatumWaarde = (DWH_REF.dbo.Get_EasterSunday(Jaar) + 39)
 
 --Bevrijdingsdag
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE jaar % 5 = 0 AND MaandNummerVanJaar = 5 AND DagNummerVanMaand = 5
 
 --1de pinkster dag
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE DatumWaarde = (DWH_REF.dbo.Get_EasterSunday(Jaar) + 49)
 
 --2de pinkster dag
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE DatumWaarde = (DWH_REF.dbo.Get_EasterSunday(Jaar) + 50)
 
 --1de Kerst
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE MaandNummerVanJaar = 12 AND DagNummerVanMaand = 25;
 
 --2de Kerst
 UPDATE REF_Datum
 SET IsFeestDag_NL = 1 
 WHERE MaandNummerVanJaar = 12 AND DagNummerVanMaand = 26;
 
 ---------------------------------------------------------------------------------
 -- Bepaal het aantal werkdagen in:
 -- Week, Maand, Kwartaal, Periode444, Periode445 en Jaar
 -- Deze informatie is handig voor bijvoorbeeld FTE Ratio bepalingen.
 ---------------------------------------------------------------------------------
 
 -- AantalWerkdagenInWeek
 -- SELECT * FROM REF_Datum
 UPDATE D
 SET D.AantalWerkdagenInWeek = ISNULL(WD.AantalWerkdagenInWeek,0)
 FROM REF_Datum AS D
 LEFT OUTER JOIN
 -- WD: WerkDagen
 (SELECT WeekNummer, COUNT(*) AS AantalWerkdagenInWeek
 FROM REF_Datum
 WHERE (IsWerkdag=1) AND (IsFeestDag_NL=0)
 GROUP BY WeekNummer
 ) AS WD
 ON (D.WeekNummer = WD.WeekNummer);
 
 -- AantalWerkdagenInMaand
 UPDATE D
 SET D.AantalWerkdagenInMaand = ISNULL(WD.AantalWerkdagenInMaand,0)
 FROM REF_Datum AS D
 LEFT OUTER JOIN
 -- WD: WerkDagen
 (SELECT Jaar, MaandNummerVanJaar, COUNT(*) AS AantalWerkdagenInMaand
 FROM REF_Datum
 WHERE (IsWerkdag=1) AND (IsFeestDag_NL=0)
 GROUP BY Jaar, MaandNummerVanJaar
 ) AS WD
 ON (D.Jaar = WD.Jaar) AND (D.MaandNummerVanJaar = WD.MaandNummerVanJaar);
 
 -- AantalWerkdagenInPeriode444
 UPDATE D
 SET D.AantalWerkdagenInPeriode444 = ISNULL(WD.AantalWerkdagenInPeriode444,0)
 FROM REF_Datum AS D
 LEFT OUTER JOIN
 -- WD: WerkDagen
 (SELECT Jaar, Periode444Nummer, COUNT(*) AS AantalWerkdagenInPeriode444
 FROM REF_Datum
 WHERE (IsWerkdag=1) AND (IsFeestDag_NL=0)
 GROUP BY Jaar, Periode444Nummer
 ) AS WD
 ON (D.Jaar = WD.Jaar) AND (D.Periode444Nummer = WD.Periode444Nummer);
 
 -- AantalWerkdagenInPeriode445
 UPDATE D
 SET D.AantalWerkdagenInPeriode445 = ISNULL(WD.AantalWerkdagenInPeriode445,0)
 FROM REF_Datum AS D
 LEFT OUTER JOIN
 -- WD: WerkDagen
 (SELECT Jaar, Periode445Nummer, COUNT(*) AS AantalWerkdagenInPeriode445
 FROM REF_Datum
 WHERE (IsWerkdag=1) AND (IsFeestDag_NL=0)
 GROUP BY Jaar, Periode445Nummer
 ) AS WD
 ON (D.Jaar = WD.Jaar) AND (D.Periode445Nummer = WD.Periode445Nummer);
 
 -- AantalWerkdagenInKwartaal
 UPDATE D
 SET D.AantalWerkdagenInKwartaal = ISNULL(WD.AantalWerkdagenInKwartaal,0)
 FROM REF_Datum AS D
 LEFT OUTER JOIN
 -- WD: WerkDagen
 (SELECT Jaar, KwartaalNummerVanJaar, COUNT(*) AS AantalWerkdagenInKwartaal
 FROM REF_Datum
 WHERE (IsWerkdag=1) AND (IsFeestDag_NL=0)
 GROUP BY Jaar, KwartaalNummerVanJaar
 ) AS WD
 ON (D.Jaar = WD.Jaar) AND (D.KwartaalNummerVanJaar = WD.KwartaalNummerVanJaar);
 
 -- AantalWerkdagenInJaar
 UPDATE D
 SET D.AantalWerkdagenInJaar = ISNULL(WD.AantalWerkdagenInJaar,0)
 FROM REF_Datum AS D
 LEFT OUTER JOIN
 -- WD: WerkDagen
 (SELECT Jaar, COUNT(*) AS AantalWerkdagenInJaar
 FROM REF_Datum
 WHERE (IsWerkdag=1) AND (IsFeestDag_NL=0)
 GROUP BY Jaar
 ) AS WD
 ON (D.Jaar = WD.Jaar);
 
 
 ---------------------------------------------------------------------------------
 -- Verloning!!!
 -- Standaard op Dinsdag (maar dan geen feestdag op maandag en dinsdag).
 ---------------------------------------------------------------------------------
 UPDATE A
 SET IsVerloningDag = 1
 FROM REF_Datum A
 LEFT OUTER JOIN REF_Datum B ON A.DatumWaarde = B.DatumWaarde + 1
 WHERE A.DagNummerVanWeek = 2 AND B.IsFeestDag_NL = 0 AND A.IsFeestDag_NL = 0;
 
 -- Woensdag - > Zet verloningsparameter voor als feestdag op maandag 
 -- (maar dan geen feestdag op woensdag en op dinsdag).
 UPDATE A
 SET IsVerloningDag = 1
 FROM REF_Datum A
 LEFT OUTER JOIN REF_Datum B ON A.DatumWaarde = B.DatumWaarde + 2 -- Maandag
 LEFT OUTER JOIN REF_Datum C ON A.DatumWaarde = C.DatumWaarde + 1 -- Dinsdag
 WHERE A.DagNummerVanWeek  = 3 AND B.IsFeestDag_NL = 1 AND C.IsFeestDag_NL = 0 AND A.IsFeestDag_NL = 0;
 
 -- Donderdag - > Zet verloningsparameter voor als feestdag op dinsdag 
 -- (maar dan geen feestdag op woensdag en donderdag). Komt niet voor?
 UPDATE A
 SET IsVerloningDag = 1
 FROM REF_Datum A
 LEFT OUTER JOIN REF_Datum B ON A.DatumWaarde = B.DatumWaarde + 2 -- Dinsdag
 LEFT OUTER JOIN REF_Datum C ON A.DatumWaarde = C.DatumWaarde + 1 -- Woensdag
 WHERE A.DagNummerVanWeek = 4 AND B.IsFeestDag_NL = 1 AND C.IsFeestDag_NL = 0 AND A.IsFeestDag_NL = 0;
 
 -- Vrijdag - > Zet verloningsparameter voor als feestdag op dinsdag en op woensdag
 -- (maar dan geen feestdag op vrijdag).
 UPDATE A
 SET IsVerloningDag = 1
 FROM REF_Datum A
 LEFT OUTER JOIN REF_Datum B ON A.DatumWaarde = B.DatumWaarde + 2 -- Woensdag
 LEFT OUTER JOIN REF_Datum C ON A.DatumWaarde = C.DatumWaarde + 3 -- Dinsdag
 WHERE A.DagNummerVanWeek  = 5 AND B.IsFeestDag_NL = 1 AND C.IsFeestDag_NL = 1 AND A.IsFeestDag_NL = 0;
 
 -- Update veld IsSelecteerbaar, om aan te geven welke dagen wel of niet
 -- zijn te selecteren in de interfaces.
 UPDATE REF_Datum
 SET IsSelecteerbaar = 1
 WHERE DatumID BETWEEN 20060000 AND ((YEAR(GETDATE())+1)*10000)
END

De dimensie kun je aanmaken door dit uit te voeren:

EXEC Generate_REF_Datum '20090101', '20110101'

 

Hier de functie om de correctie op feestdagen te bepalen:

CREATE FUNCTION [dbo].[Get_EasterSunday](@iYear AS INT)
RETURNS SMALLDATETIME
AS
 
BEGIN
 DECLARE @ePactCalc            INT
 DECLARE @PaschalDaysCalc    INT
 DECLARE @NumofDaysToSunday    INT
 DECLARE @EasterMonth        INT
 DECLARE @EasterDay            INT
 
 SET @ePactCalc = (24 + 19 * (@iYear %19)) % 30
 SET @PaschalDaysCalc = @ePactCalc - (@ePactCalc/28)
 SET @NumOfDaysToSunday = @PaschalDaysCalc - ((@iYear + @iYear/4 + @PaschalDaysCalc - 13) % 7)
 SET @EasterMonth = 3 + (@NumOfDaysToSunday + 40)/44
 SET @EasterDay = @NumOfDaysToSunday + 28 - (31*(@EasterMonth/4))
 
 RETURN(SELECT CONVERT(SMALLDATETIME, RTRIM(@iYear) + RIGHT('0' + RTRIM(@EasterMonth), 2) + RIGHT('0' + RTRIM(@EasterDay), 2)))
END

dd_dimdatum

Als we nu de datumdimensie aanmaken in de AdventureWorks database (dus niet het dwh van adventureworks) kun je de kracht zien. We gaan de datumdimensie joinen aan de OrderDate. Vervolgens kunnen we eenvoudig meerdere eigenschappen voor deze datum vinden!

SELECT TOP 100
 SOH.SalesOrderID,
 SOH.OrderDate,
 DD.DatumId,
 DD.WeekNummer,
 DD.MaandNummerVanJaar,
 DD.Kwartaal445Code,
 DD.Kwartaal445Nummer        
FROM Sales.SalesOrderHeader AS SOH
-- Hier zetten we de OrderDate om naar een integer en joinen hem met de Datum-dimensie DimDatum
LEFT OUTER JOIN DimDatum AS DD ON YEAR(SOH.OrderDate) * 10000 + MONTH(SOH.OrderDate) * 100 + DAY(SOH.OrderDate) = DD.DatumId

dd_adventureworks

Tags Bekijk alle Tags


SQL 
Copyright 2008. All Rights Reserved.

Bijlagen:
Download dit bestand (Generate_REF_Datum.sql)Generate_REF_Datum.sql[ ]48 Kb
Download dit bestand (GET_EasternSunday.sql)GET_EasternSunday.sql[ ]1 Kb
Download dit bestand (Get_ISOWeekNo.sql)Get_ISOWeekNo.sql[ ]1 Kb

Trackback(0)

TrackBack URI voor deze post

Commentaar (5)

RSS feed Commentaar
...
76
Huge script en zeer handig! Gecopied enzo voor verder gebruik smilies/smiley.gif
Mark Waterreus , februari 24, 2009
...
63
Sorry, ik kon hem niet verkleinen. Uiteraard hoef je niet alles te gebruiken maar het gaat om de opzet. En lekker in het Nederlands uiteraard. Misschien zijn er nog velden die generiek zijn die er in opgenomen kunnen worden, hoewel ik denk dat het meeste er wel inzit.smilies/wink.gif
Ronald Kraijesteijn , februari 24, 2009
...
0
Ziet er fantastisch uit! smilies/cheesy.gif Maar mis ik nou de functie Get_ISOWeekNo of zie ik hem over het hoofd?
Philippe Navarre , maart 13, 2009
...
62
Ik heb hem voor je erbij gezet, lukt het zo verder?
Administrator , maart 16, 2009
...
0
Ja, het is gelukt met de iso-weeknummers. Bedankt!
Philippe Navarre , april 24, 2009

Schrijf commentaar

kleiner | groter
security image
Schrijf de volgende tekens

busy