Omdat mijn huidige klant met onderwijs te maken heeft, moest de kalender dimensie alle schoolvakanties bevatten. Deze berekenen kan aan de hand van, jawel, enkele regels die je kan terugvinden op de website van de Vlaamse Overheid.
Met de hulp van een paar berekeningen die het internet reeds voor mij deed (een stored procedure om Pasen te berekenen en eentje om een weekdag van een bepaalde week te kunnen vinden):
CREATE FUNCTION dbo.udf_nthWeekDay ( @n INT, @weekDay CHAR(3), @year INT, @month INT ) RETURNS DATETIME AS BEGIN DECLARE @date DATETIME, @dow INT, @offset INT, @wd INT; SELECT @wd = CASE @weekDay WHEN 'SUN' THEN 1 WHEN 'MON' THEN 2 WHEN 'TUE' THEN 3 WHEN 'WED' THEN 4 WHEN 'THU' THEN 5 WHEN 'FRI' THEN 6 WHEN 'SAT' THEN 7 END, @date = CAST ( CAST(@year AS VARCHAR(4)) + RIGHT ( '0' + CAST ( @month AS VARCHAR(2) ), 2 ) + '01' AS DATETIME ), @dow = DATEPART(dw, @date), @offset = @wd - @dow, @date = DATEADD(day, @offset + (@n - CASE WHEN @offset >= 0 THEN 1 ELSE 0 END) * 7, @date); RETURN @date; END; GO CREATE FUNCTION [dbo].[fn_EasterSundayByYear] (@Year char(4)) RETURNS smalldatetime AS BEGIN declare @c int , @n int , @k int , @i int , @j int , @l int , @m int , @d int , @Easter datetime set @c = (@Year / 100) set @n = @Year - 19 * (@Year / 19) set @k = (@c - 17) / 25 set @i = @c - @c / 4 - ( @c - @k) / 3 + 19 * @n + 15 set @i = @i - 30 * ( @i / 30 ) set @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11)) set @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4 set @j = @j - 7 * (@j / 7) set @l = @i - @j set @m = 3 + (@l + 40) / 44 set @d = @l + 28 - 31 * ( @m / 4 ) set @Easter = (select right('0' + convert(varchar(2),@m),2) + '/' + right('0' + convert(varchar(2),@d),2) + '/' + convert(char(4),@Year)) return @Easter END CREATE TABLE [dbo].[DimKalender]( [KalenderId] [int] NOT NULL, [KalenderDatum] [datetime] NOT NULL, [KalenderDag] [int] NOT NULL, [KalenderMaandNaam] [varchar](20) NOT NULL, [KalenderMaand] [int] NOT NULL, [KalenderTrimester] [int] NOT NULL, [KalenderSemester] [int] NOT NULL, [KalenderJaar] [int] NOT NULL, [KalenderSchooljaar] [varchar](9) NOT NULL, [KalenderVakantieNaam] [varchar] (10) NOT NULL, CONSTRAINT [DimKalender_PK_IT] PRIMARY KEY CLUSTERED ( [KalenderId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
… heb ik om de tabel te vullen, en dus effectief de schoolvakanties te berekenen dit in elkaar gestoken:
SET DATEFIRST 1 DECLARE @startdate DATETIME DECLARE @enddate DATETIME DECLARE @year INT DECLARE @yearasstring VARCHAR(4) DECLARE @month INT DECLARE @prevyearasstring VARCHAR(4) DECLARE @nextyearasstring VARCHAR(4) DECLARE @easter DATETIME DECLARE @day INT DECLARE @twoweeksaftereaster DATETIME DECLARE @firstaprilmonday DATETIME DECLARE @xmas DATETIME DECLARE @xmasprevyear DATETIME DECLARE @xmasdow INT DECLARE @xmasdowprevyear INT DECLARE @eastermonday DATETIME DECLARE @eastermonth INT DECLARE @weekday INT SET @startdate = CONVERT(DATETIME,'01-01-1990') SET @enddate = CONVERT(DATETIME,'01-01-2100') WHILE @startdate < @enddate BEGIN SET @year = DATEPART(yy,@startdate) SET @yearasstring = CAST(@year AS VARCHAR(4)) SET @month = DATEPART(MM,@startdate) SET @prevyearasstring = CAST((@year-1) AS VARCHAR(4)) SET @nextyearasstring = CAST((@year+1) AS VARCHAR(4)) SET @easter = [dbo].fn_EasterSundayByYear(@yearasstring) SET @day = DATEPART(dd,@startdate) SET @twoweeksaftereaster = DATEADD(DD,14,@easter) SET @firstaprilmonday = dbo.udf_nthWeekDay(1,'MON',@year,4) SET @xmas = ('12-25-')+ @yearasstring SET @xmasprevyear = ('12-25-')+ @prevyearasstring SET @xmasdow = DATEPART(DW,@xmas) SET @xmasdowprevyear = DATEPART(DW,('12-25-')+ @prevyearasstring) SET @eastermonday = DATEADD(DD,1,@easter) SET @eastermonth = DATEPART(MM,(@easter)) SET @weekday = DATEPART(DW,@startDate) INSERT INTO [dbo].[DimKalender] ([KalenderId] ,[KalenderDatum] ,[KalenderDag] ,[KalenderMaand] ,[KalenderMaandNaam] ,[KalenderTrimester] ,[KalenderJaar] ,[KalenderSchoolJaar] ,[KalenderVakantieNaam] ) VALUES ( /*[KalenderId]*/ @year*10000 + @month*100 + @day, /*[KalenderDatum]*/ @startdate, /*[KalenderDag]*/ @day, /*[KalenderMaand]*/ @month, /*[KalenderMaandNaam]*/ CASE @month WHEN 1 THEN 'Januari' WHEN 2 THEN 'Februari' WHEN 3 THEN 'Maart' WHEN 4 THEN 'April' WHEN 5 THEN 'Mei' WHEN 6 THEN 'Juni' WHEN 7 THEN 'Juli' WHEN 8 THEN 'Augustus' WHEN 9 THEN 'September' WHEN 10 THEN 'Oktober' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END, /*[KalenderTrimester]*/ DATEPART(qq, @startdate), /*[KalenderJaar]*/ @year, /*[KalenderSchooljaar]*/ CASE WHEN @month <= 8 THEN @prevyearasstring + '-' + @yearasstring ELSE @yearasstring + '-' + @nextyearasstring END, /*[KalenderVakantieNaam]*/ CASE WHEN @month = 7 THEN 'Zomer' WHEN @month = 8 THEN 'Zomer' WHEN DATEPART(dw,('11-01-' + @yearasstring)) = 7 AND (@startDate between ('11-02-'+ @yearasstring) and ('11-08-'+ @yearasstring)) THEN 'Herfst' WHEN DATEPART(dw,('11-01-' + @yearasstring)) != 7 AND DATEPART(WW,@startdate) = DATEPART(WW,('11-01-'+@yearasstring)) THEN 'Herfst' WHEN @month = 12 AND @xmasdow = 6 AND @startdate between ('12-27-' + @yearasstring) and ('01-09-' + @nextyearasstring) THEN 'Kerst' WHEN @month = 12 AND @xmasdow = 7 AND @startdate between ('12-26-' + @yearasstring) and ('01-08-' + @nextyearasstring) THEN 'Kerst' WHEN @month = 12 AND @xmasdow < 6 AND @startdate between (DATEADD (dd,(-(@weekday - 1)),(@xmas))) and (DATEADD (dd,(- @weekday + 14),(@xmas))) THEN 'Kerst' WHEN @month = 1 AND @xmasdowprevyear = 6 AND @startdate <= ('01-09-' + @yearasstring) THEN 'Kerst' WHEN @month = 1 AND @xmasdowprevyear = 7 AND @startdate <= ('01-08-' + @yearasstring) THEN 'Kerst' WHEN @month = 1 AND @xmasdowprevyear < 6 AND @startdate between (DATEADD (dd,(-(@weekday - 1)),(@xmasprevyear))) and (DATEADD (dd,(- @weekday + 14),(@xmasprevyear))) THEN 'Kerst' WHEN @startdate = '11-11-' + @yearasstring THEN '11 November' WHEN @startdate = '05-01-' + @yearasstring THEN '1 Mei' WHEN @eastermonth = 3 AND @startdate between @eastermonday AND @twoweeksaftereaster THEN 'Pasen' WHEN @easter > ('04-15-' + @yearasstring) AND @startdate between (DATEADD(DD,-13,@easter)) AND @eastermonday THEN 'Pasen' WHEN @eastermonth != 3 AND @easter <= ('04-15-' + @yearasstring) AND @startdate between @firstaprilmonday AND DATEADD(DD,13,@firstaprilmonday) THEN 'Pasen' WHEN @startdate = DATEADD(DD,49,@twoweeksaftereaster) THEN 'PinksterMaandag' WHEN @startdate between DATEADD(DD,-48,@easter) AND DATEADD(DD,-42,@easter) THEN 'Krokus' WHEN @startdate between DATEADD(DD,39,@easter) AND DATEADD(DD,40,@easter) THEN 'Hemelvaart' ELSE 'Geen' END ) SET @startdate = DATEADD(dd,1,@startdate) END
Enjoy ;)