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 ;)