Schoolvakanties in SQL

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

Stekker

Nog zoveel te vertellen: Kunst om 8, nieuwe AB site,… En toch zal het even moeten wachten, want Evelyne en ik trekken er vandaag een dagje op uit, het begin van een weekje vakantie.

Vanavond steek ik de stekker terug in.

Terug

Goeiemorgen! Ik ben terug en heb een kleur. Ik heb Lissabon afgewandeld (3 dagen) en ik heb mijn batterijen kunnen opladen aan het zwembad van een droomhotel in Cascais (4 dagen).
Ik heb beslissingen kunnen nemen en ik heb twee toffe mensen die ik al zeer goed kende nog beter leren kennen. Ik heb genoten en gelachen, goed gegeten en gedronken en dit alles samen met de liefde van mij leven.

Beste vrienden, aan de andere kant van het internet zit een nieuwe Kevin. Een man die geen zin meer heeft zijn tijd te verdoen. Een man die gekozen heeft voor een leven vol genot. Voor degelijk werk en veel plezier. Plezier in mijn job, plezier in mijn relatie, plezier met mijn familie en plezier in mijn vrije tijd. Ik kies voor verandering en deze zal vandaag nog in gang worden gestoken. Hoeveel jij er van zal merken hangt er een beetje van af hoe dicht je bij mij staat in het echte leven, maar iets zal je er wel van merken. Maar kom, genoeg geleuterd, geen woorden maar daden hoor ik u zeggen en u hebt groot gelijk.

Een reisverslag inclusief foto’s mag u uiteraard nog wel verwachten.
Oh ja, C heeft zijn uiterste best gedaan mij het correcte gebruik van “heten en noemen“ in te drillen, maar ik vrees dat dit nog een zeer lange en vermoeiende strijd zal worden, om het woord hopeloos maar niet te gebruiken.

Limburg

Het zijn drukke dagen voor ik van welverdiende rust mag genieten. Elke dag naar Hasselt rijden, ik ben zulke afstanden niet meer gewoon. Maar tussen Antwerpen en Hasselt kan je rijden, een groot verschil met de verbinding Antwerpen-Brussel, dat moet ik u waarschijnlijk niet zeggen (hoewel het dezer dagen van vakantie ook daar wel zal meevallen). De klant is gelegen in de Limburg, nabij plaatsen met klinkende namen als Hoepertingen, Kuttekoven en Zepperen. Mooie streken waar je graag even je auto zou willen stilleggen als je voorbij rijen fruitbomen in de vroege ochtendzon rijdt. De makers van Katarakt hadden gelijk.
Storm voor de stilte, hoewel het hier al twee dagen stil is, waarvoor mijn oprechte excuses. Maar er volgen er helaas nog, want we vertrekken voor een dikke week naar Portugal.
Vakantie… ja, graag. Het is nodig, er spookt veel door mijn hoofd. Veel veranderingen, veel beslissingen, veel nieuws. Je hoort er nog wel van.

Later. Tot binnenkort.

Er even tussenuit

U kon het al raden, ik trap het even af. Even kijken:

  • Tickets? Yesh!
  • Voor Gimlet wordt gezorgd? Check.
  • House-sitter? Ok.
  • Planten genoeg water? Ik hoop het.
  • Ingepakt? Mee bezig.
  • Werkmail heeft Out-of-office ingesteld? Done.
  • Op Twitter gezegd dat ik weg ben? Is dat nodig?
  • Op blog? Nu wel.
  • Alle betalingen gedaan? Yup.
  • Niets vergeten? Mee bezeug!

Bon, tot binnen een dikke week dan maar :)
Toedels!