Tag: business intelligence

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