Kalender i SQL

af Joakim Dalby

 

Jeg kan lide at have en kalender i en tabel i en database med de kolonner der er relevante for løsnin­gen. Jeg opbygger kalenderen gennem med data i en hjælpetabeller og en stored procedure til at danne kalenderens datarækker med. En kalender kan bruges i mange sammenhænge bl.a. i en database til hjælp for et søgekriterie for et ugenummer og i et data warehouse til en Dato dimension, hvor et sql view kan begrænse antallet af viste datoer f.eks. fra 2000 frem til og med måneden med det fulde data­grundlag i datavarehuset.

 

dbo.Kalender – kolonner og eksempel på en datarække for datoen 1. janaur 2000:

 

Kolonnenavn

Datatype

Forklaring

Eksempel

Dato

date

En ægte dato i kalenderen

2000-01-01

ÅrDato

int

Datoen som et heltal

20000101

Ugedag

nvarchar(7)

Datoens navn i ugen

Lørdag

Måned

nvarchar(9)

Datoen tilhører en måned med et navn

Januar

Helligdag

nvarchar(21)

Datoens evt. navn som helligdag

Nytårsdag

Øvrigdag

nvarchar(12)

Datoens evt. navn som øvrigdag

null

ErHelligdag

bit

Datoens markering som helligdag (0/1)

1

ErHverdag

bit

Datoens markering som hverdag (0/1)

0

ErArbejdsdag

bit

Datoens markering som arbejdsdag (0/1)

0

ErLukkedag

bit

Datoens markering som lukkedag (0/1)

0

ErFørsteMåned

bit

Datoens markering som første dag i måneden

1

ErSidsteMåned

bit

Datoens markering som sidste dag i måneden

0

ErFørsteKvartal

bit

Datoens markering som første dag i kvartalet

1

ErSidsteKvartal

bit

Datoens markering som sidste dag i kvartalet

0

DagIUge

tinyint

Datoens dagsnummer i ugen, mandag = 1 (1-7)

6

DagIMåned

tinyint

Datoens dagsnummer i måneden (1-31)

1

DagIÅr

smallint

Datoens dagsnummer i året (1-366)

1

Ugenummer

tinyint

Datoen tilhører et ugenummer i Danmark (1-53)

52

Månedsnummer

tinyint

Datoen tilhører et månedsnummer (1-12)

1

Kvartalsnummer

tinyint

Datoen tilhører et kvartalsnummer (1-4)

1

Halvårsnummer

tinyint

Datoen tilhører et halvårsnummer (1-2)

1

År

smallint

Datoen tilhører et år (årstal for datoen)

2000

UgedagKort

nvarchar(3)

Datoens navn i ugen som tre bogstaver

LØR

DagHelligdag

nvarchar(25)

Navn i ugen tre bogstaver evt / helligdag

LØR/Nytårsdag

MånedKort

nvarchar(3)

Datoens navn i måneden som tre bogstaver

JAN

Kvartal

nvarchar(10)

Datoen tilhører et kvartal med et navn

1. kvartal

Halvår

nvarchar(9)

Datoen tilhører et halvår med et navn

1. halvår

UgeStartÅr

smallint

Datoens ugenummer starter i et år

1999

UgeStartdato

date

Datoens ugenummer starter på en dato

1999-12-27

UgeSlutdato

date

Datoens ugenummer slutter på en dato

2000-01-02

MånedStartdato

date

Datoens måned starter på en dato

2000-01-01

MånedSlutdato

date

Datoens måned slutter på en dato

2000-01-31

KvartalStartdato

date

Datoens kvartal starter på en dato

2000-01-01

KvartalSlutdato

date

Datoens kvartal slutter på en dato

2000-03-31

HalvårStartdato

date

Datoens halvår starter på en dato

2000-01-01

HalvårSlutdato

date

Datoens halvår slutter på en dato

2000-06-30

ÅrStartdato

date

Datoens år starter på en dato

2000-01-01

ÅrSlutdato

date

Datoens år slutter på en dato

2000-12-31

UgeSidsteHverdagDato

date

Datoens ugenummer slutter på en hverdag

1999-12-31

MånedSidsteHverdagDato

date

Datoens måned slutter på en hverdag

2000-01-31

KvartalSidsteHverdagDato

date

Datoens kvartal slutter på en hverdag

2000-03-31

HalvårSidsteHverdagDato

date

Datoens halvår slutter på en hverdag

2000-06-30

ÅrSidsteHverdagDato

date

Datoens år slutter på en hverdag

2000-12-30

ÅrUge

int

Datoens år og ugenummer som et heltal

199952

ÅrMåned

int

Datoens år og månedsnummer som et heltal

200001

ÅrKvartal

int

Datoens år og kvartalsnummer som et heltal

200001

ÅrHalvår

int

Datoens år og halvårsnummer som et heltal

200001

DagIUge0

nvarchar(2)

DagIUge med evt. foranstillet 0 som to cifre

06

DagIMåned0

nvarchar(2)

DagIMåned med evt. foranstillet 0

01

Ugenummer0

nvarchar(2)

Ugenummer med evt. foranstillet 0

52

Månedsnummer0

nvarchar(2)

Månedsummer med evt. foranstillet 0

01

ErUgeOver2Måneder

bit

Datoens uge går over to måneder (0/1)

1

Årstid

nvarchar(10)

Datoens årstid i Danmark med fire årstider

Vinter

Sæson

nvarchar(9)

Datoens sæson fra 1. juli årx til 30. juni årx+1

1999/2000

DageIÅr

smallint

Antallet af dage i datoens år (1-366)

366

DageIHalvår

smallint

Antallet af dage i datoens halvår (1-184)

182

DageIKvartal

smallint

Antallet af dage i datoens kvartal (1-92)

91

DageIMåned

smallint

Antallet af dage i datoens måned (1-31)

31

ÅrUgeDagIUge

int

ÅrUge (seks cifre) og DagIUge (et ciffer)

1999526

UgeDagIUge

int

ÅrUgeDagIUge uden årstal f.eks. 11 = uge 1 mandag, 12 = uge 1 tirsdag, 73 = uge 7 onsdag, 537 = uge 53 søndag til at sammenligne uger over flere år uden at anvende datoer (11-537)

526

HelUgePrÅr_DagIÅr

smallint

For leap week eller intercalary week (1-371)

363

HelUgePrÅr_År

smallint

Årstallet for HelUgePrÅr_DagIÅr

1999

Arbejdsdagsnummer

int

Fortløbende nummer på arbejdsdage

22677

InsertTime_meta

datetime2(3)

Datarækkens indsættelsestidspunkt

Default value

 

Hjælpetabeller

 

dbo.Helligdag ud fra søndag Påskedag kan andre helligdage udregnes med et fast antal dage og tre hel­lig­dage har fast dato f.eks. juledag er altid den 25. december uanset år, d.v.s. 25 dag og 12 måned. Danmark har 9 helligdage hvoraf de fleste ikke falder på en søndag, men jeg har valgt at medtage tre ekstra søndage, så de får et Helligdag i kalenderen, det er Palmesøndag, Påskedag og Pinsedag:

 

Helligdag

Antaldage

FraPåskedag

FastDag

FastMåned

FraÅr

TilÅr

Nytårsdag

null

1

1

1

9999

Palmesøndag

-7

null

null

1

9999

Skærtorsdag

-3

null

null

1

9999

Langfredag

-2

null

null

1

9999

Påskedag

0

null

null

1

9999

2. Påskedag

1

null

null

1

9999

Store Bededag

26

null

null

1

2023

Kristi Himmelfartsdag

39

null

null

1

9999

Pinsedag

49

null

null

1

9999

2. Pinsedag

50

null

null

1

9999

Juledag

null

25

12

1

9999

2. Juledag

null

26

12

1

9999

 

Datoen for Påskedag bestemmes ud fra en matematisk formel med et årstal som parameter.

Palmesøndag, Påskedag og Pinsedag er medtaget som helligdage selvom de altid er på en søndag og derfor normalt ikke regnes som en af de ni helligdage i Danmark, men det er gjort for at få udfyldt kolonnerne Helligdag og DagHelligdag, og ønskes det ikke, så kan de tre datarækker slettes i tabellen dbo.Helligdag.

 

dbo.Øvrigdag praktisk at kunne angive andre dage der ikke skal indgå som arbejdsdage:

 

Øvrigdag

FastDag

FastMåned

FraÅr

TilÅr

Grundlovsdag

5

6

1

9999

Juleaften

24

12

1

9999

Nytårsaften

31

12

1

9999

 

dbo.Ugedag for på dansk at angive navnene på de syv ugedage og deres forkortelse:

 

DagIUge

Ugedag

UgedagKort

1

Mandag

MAN

2

Tirsdag

TIR

3

Onsdag

ONS

4

Torsdag

TOR

5

Fredag

FRE

6

Lørdag

LØR

7

Søndag

SØN

 

dbo.Måned for på dansk at angive navnene på de 12 måneder og deres forkortelse m.v.:

 

Månedsnummer

Måned

MånedKort

Kvartal

Halvår

Årstid

1

Januar

JAN

1. kvartal

1. halvår

Vinter

2

Februar

FEB

1. kvartal

1. halvår

Vinter

3

Marts

MAR

1. kvartal

1. halvår

Forår

4

April

APR

2. kvartal

1. halvår

Forår

5

Maj

MAJ

2. kvartal

1. halvår

Forår

6

Juni

JUN

2. kvartal

1. halvår

Sommer

7

Juli

JUL

3. kvartal

2. halvår

Sommer

8

August

AUG

3. kvartal

2. halvår

Sommer

9

September

SEP

3. kvartal

2. halvår

Efterår

10

Oktober

OKT

4. kvartal

2. halvår

Efterår

11

November

NOV

4. kvartal

2. halvår

Efterår

12

December

DEC

4. kvartal

2. halvår

Vinter

 

dbo.Lukkedag praktisk at kunne angive dage der er lukkedage (ikke modsat arbejdsdag):

 

Lukkedag

AntaldageFra

Påskedag

Fast

Dag

Fast

Måned

FastUge

nummer

FraÅr

TilÅr

Fredag efter

Kristi Himmelfartsdag

40

null

null

null

1

9999

Grundlovsdag

null

5

6

null

1

9999

Juleferie

null

24

12

null

1

9999

Juleferie

null

27

12

null

1

9999

Juleferie

null

28

12

null

1

9999

Juleferie

null

29

12

null

1

9999

Juleferie

null

30

12

null

1

9999

Juleferie

null

31

12

null

1

9999

Vinterferie

null

null

null

7

1

9999

Sommerferie

null

null

null

28

1

9999

Sommerferie

null

null

null

29

1

9999

Sommerferie

null

null

null

30

1

9999

 

Datoen for Påskedag bestemmes ud fra en matematisk formel med et årstal som parameter.

Eksempel på kald af funktionen med årstallet 2000:

 

SELECT Påskedag = dbo.Påskedag(2000)

Giver: 2000-04-23.

 

-- =============================================

-- Author:      Joakim Dalby

-- Create date: 23-11-2014

-- Description: Dannelse af Påskedag dato

-- SELECT Påskedag = dbo.Påskedag(2000)

-- =============================================

CREATE FUNCTION [dbo].[Påskedag](@år int)

RETURNS date

AS

BEGIN

  DECLARE @a int, @b int, @c int, @d int, @e int, @k int, @p int, @q int, @m int, @n int,

          @dag int, @mnd int

 

  SET @k = @år / 100

  SET @p = (13 + 8 * @k) / 25

  SET @q = @k / 4

  SET @m = (15 - @p + @k - @q) % 30

  SET @n = (4 + @k - @q) % 7

  SET @a = @år % 19

  SET @b = @år % 4

  SET @c = @år % 7

  SET @d = (19 * @a + @m) % 30

  SET @e = (2 * @b + 4 * @c + 6 * @d + @n) % 7

 

  IF @d + @e <= 9

  BEGIN

    SET @dag = 22 + @d + @e

    SET @mnd = 3

  END

  ELSE IF (@d = 29) AND (@e = 6)

       BEGIN

         SET @dag = 19

         SET @mnd = 4

       END

       ELSE IF (@d = 28) And (@e = 6) And (@a > 10)

            BEGIN

              SET @dag = 18

              SET @mnd = 4

            END

            ELSE

            BEGIN

              SET @dag = @d + @e - 9

              SET @mnd = 4

            END

 

  RETURN DATEFROMPARTS(@år, @mnd, @dag)

END

 

Kalender tabellen får indsat datarækker gennem kald af en stored procedure med et årstalsinterval som parametre.

Eksempler på kald af stored proceduren med årstalsintervallet fra 1900 til og med 2100:

 

EXEC dbo.Kalender_Build 1900, 2100

EXEC dbo.Kalender_Build    1, 9999

 

-- =============================================

-- Author:      Joakim Dalby

-- Create date: 23-11-2014

-- Description: Dannelse af Kalender

-- EXEC dbo.Kalender_Build 1900, 2100

-- EXEC dbo.Kalender_Build    1, 9999

-- Changelog:

--   12-03-2023 Folketinget har afskaffet Store Bededag med virkning fra 2024.

--              Tabellerne Helligdag og Øvrigdag er udvidet med felter FraÅr og TilÅr

--              som indgår i udfyldelsen af felterne Helligdag og Øvrigdag.

--   27-08-2023 Tilføjet ErLukkedag og Arbejdsdagsnummer.

-- =============================================

CREATE PROCEDURE [dbo].[Kalender_Build]

       @startÅr smallint, @slutÅr smallint

AS

BEGIN

 SET NOCOUNT ON

 

 SET LANGUAGE Danish -- SELECT * FROM sys.syslanguages

 SET DATEFIRST 1     -- Mandag er første dag i en uge og får DagIUge = 1

 

 DECLARE @dato date

 

 SET @dato = DATEFROMPARTS(@startÅr, 1, 1)

 

 TRUNCATE TABLE dbo.Kalender

 

 -- Løkke der danner en datarække per dato.

 WHILE YEAR(@dato) <= @slutÅr

 BEGIN

  INSERT INTO dbo.Kalender(Dato, ErHelligdag, ErHverdag, ErArbejdsdag, ErLukkedag,

  ErUgeOver2Måneder, ErFørsteMåned, ErSidsteMåned, ErFørsteKvartal, ErSidsteKvartal,

  ÅrDato, Ugedag, Måned, DagIUge, DagIMåned, DagIÅr,

  Ugenummer, Månedsnummer, Kvartalsnummer, Halvårsnummer,

  År, ÅrUge, ÅrMåned, ÅrKvartal, ÅrHalvår)

  VALUES

  (

   @dato, --Dato

   0,     --ErHelligdag

   0,     --ErHverdag

   0,     --ErArbejdsdag

   0,     --ErLukkedag

   0,     --ErUgeOver2Måneder

   0,     --ErFørsteMåned

   0,     --ErSidsteMåned

   0,     --ErFørsteKvartal

   0,     --ErSidsteKvartal

   YEAR(@dato) * 10000 + MONTH(@dato) * 100 + DAY(@dato), --ÅrDato

   DATENAME(weekday, @dato),  --Ugedag

   DATENAME(month, @dato),    --Måned

   DATEPART(weekday, @dato),  --DagIUge

   DATEPART(day, @dato),      --DagIMåned

   DATEPART(dayofyear, @dato),--DagIÅr

   DATEPART(iso_week, @dato), --Ugenummer, DATEPART(week, @dato) anvender mandag til at bestemme uge 1,

                              --Danmark bruger torsdag til at bestemme uge 1 i det nye år.

   DATEPART(month, @dato),    --Månedsnummer

   DATEPART(quarter, @dato),  --Kvartalsnummer

   (DATEPART(quarter, @dato) + 1) / 2,--Halvårsnummer, heltalsdivision

   DATEPART(year, @dato),     --År

 -- ÅrUge hvor f.eks. søndag den 1. januar 2012 tilhører uge 52 som kommer fra året 2011 så ÅrUge = 201152

   CASE WHEN DATEPART(iso_week, @dato) = 1 AND YEAR(@dato) < YEAR(DATEADD(day, 7, @dato))

          THEN YEAR(DATEADD(day,7, @dato)) * 100 + DATEPART(iso_week, @dato)

        WHEN DATEPART(iso_week, @dato) >= 52 AND YEAR(@dato) > YEAR(DATEADD(day, -7, @dato))

          THEN YEAR(DATEADD(day, -7, @dato)) * 100 + DATEPART(iso_week, @dato)

        ELSE YEAR(@dato) * 100 + DATEPART(iso_week, @dato)

   END,

   DATEPART(year, @dato) * 100 + DATEPART(month, @dato),            --ÅrMåned

   DATEPART(year, @dato) * 100 + DATEPART(quarter, @dato),          --ÅrKvartal

   DATEPART(year, @dato) * 100 + (DATEPART(quarter, @dato) + 1) / 2 --ÅrHalvår

  )

  IF @dato <> '99991231'

     SET @dato = DATEADD(day, 1, @dato)

  ELSE

     BREAK

 END  -- Løkken slutter.

 

 -- En række opdateringer af alle dato rækkerne i tabellen dbo.Kalender.

 

 -- UgeStartÅr.

 

 UPDATE k

 SET    UgeStartÅr = ÅrUge / 100

 FROM   dbo.Kalender k

 

 -- Når en helligdag f.eks. juledag falder på en søndag, så tilsidesættes Søndag med Juledag.

 

 UPDATE k

 SET    Helligdag = 'Søndag', ErHelligdag = 1

 FROM   dbo.Kalender k

 WHERE  DagIUge = 7

 

 UPDATE k

 SET    Helligdag = h.Helligdag, ErHelligdag = 1

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT k.Dato, h.Helligdag

    FROM dbo.Kalender k

      INNER JOIN

      (

       SELECT å.År, Påskedag = dbo.Påskedag(å.År)

       FROM (SELECT DISTINCT År FROM dbo.Kalender) å

      ) p ON p.År = k.År

      INNER JOIN dbo.Helligdag h ON

         DATEFROMPARTS(k.År, h.FastMåned, h.FastDag) = k.Dato OR    -- helligdag på en fast dag og måned

         DATEDIFF(day, p.Påskedag, k.Dato) = h.AntaldageFraPåskedag -- helligdag antal dage fra Påskedag

    WHERE k.År BETWEEN h.FraÅr AND h.TilÅr

   ) h ON h.Dato = k.Dato

 

 -- Øvrigdag som ikke er en offentlig helligdag.

 

 UPDATE k

 SET    Øvrigdag = ø.Øvrigdag

 FROM   dbo.Kalender k

   INNER JOIN dbo.Øvrigdag ø ON DATEFROMPARTS(k.År, ø.FastMåned, ø.FastDag) = k.Dato

 WHERE k.År BETWEEN ø.FraÅr AND ø.TilÅr

 

 -- Hverdag er det modsatte af en Helligdag.

 

 UPDATE k

 SET    ErHverdag = 1

 FROM   dbo.Kalender k

 WHERE  ErHelligdag = 0

 

 -- Arbejdsdag er mandag til fredag og ikke en helligdag og ikke en øvrig dag.

 

 UPDATE k

 SET    ErArbejdsdag = 1

 FROM   dbo.Kalender k

 WHERE  DagIUge BETWEEN 1 AND 5 AND ErHelligdag = 0 AND Øvrigdag IS NULL

 

 -- Lukkedag.

 

 UPDATE k

 SET    ErLukkedag = 1

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT k.Dato, h.Lukkedag

    FROM dbo.Kalender k

      INNER JOIN

      (

       SELECT å.År, Påskedag = dbo.Påskedag(å.År)

       FROM (SELECT DISTINCT År FROM dbo.Kalender) å

      ) p ON p.År = k.År

      INNER JOIN dbo.Lukkedag h ON

         DATEFROMPARTS(k.År, h.FastMåned, h.FastDag) = k.Dato OR

         DATEDIFF(day, p.Påskedag, k.Dato) = h.AntaldageFraPåskedag OR

         k.Dato IN (SELECT Dato FROM dbo.Kalender u WHERE u.ÅrUge = (k.År * 100) + h.FastUgenummer)

    WHERE k.År BETWEEN h.FraÅr AND h.TilÅr

   ) h ON h.Dato = k.Dato

 

-- UgedagKort og Ugedag med stort bogstav fra Ugedag tabellen.

 

 UPDATE k

 SET    UgedagKort = d.UgedagKort, Ugedag = d.Ugedag,

        DagHelligdag = d.UgedagKort + IIF(k.Helligdag IS NOT NULL,'/'+k.Helligdag,'')

 FROM   dbo.Kalender k

   INNER JOIN dbo.Ugedag d ON d.DagIUge = k.DagIUge

 

 -- MånednavnKort, Kvartalnavn og Halvårnavn samt Månednavn med stort bogstav fra Måned tabellen.

 

 UPDATE k

 SET    MånedKort = m.MånedKort, Måned = m.Måned, Kvartal = m.Kvartal, Halvår = m.Halvår,

        Årstid = m.Årstid

 FROM   dbo.Kalender k

   INNER JOIN dbo.Måned m ON m.Månedsnummer = k.Månedsnummer

 

 -- UgeStartdato og UgeSlutdato.

 

 UPDATE k

 SET    UgeStartdato = d.minDato, UgeSlutdato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrUge, minDato = MIN(Dato), maxDato = MAX(Dato)

    FROM dbo.Kalender

    GROUP BY ÅrUge

   ) d ON d.ÅrUge = k.ÅrUge

 

  -- MånedStartdato og MånedSlutdato.

 

 UPDATE k

 SET    MånedStartdato = d.minDato, MånedSlutdato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrMåned, minDato = MIN(Dato), maxDato = MAX(Dato)

    FROM dbo.Kalender

    GROUP BY ÅrMåned

   ) d ON d.ÅrMåned = k.ÅrMåned

 

  -- KvartalStartdato og KvartalSlutdato.

 

 UPDATE k

 SET    KvartalStartdato = d.minDato, KvartalSlutdato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrKvartal, minDato = MIN(Dato), maxDato = MAX(Dato)

    FROM dbo.Kalender

    GROUP BY ÅrKvartal

   ) d ON d.ÅrKvartal = k.ÅrKvartal

 

 -- HalvårStartdato og HalvårSlutdato.

 

 UPDATE k

 SET    HalvårStartdato = d.minDato, HalvårSlutdato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrHalvår, minDato = MIN(Dato), maxDato = MAX(Dato)

    FROM dbo.Kalender

    GROUP BY ÅrHalvår

   ) d ON d.ÅrHalvår = k.ÅrHalvår

 

 -- ÅrStartdato og ÅrSlutdato.

 

 UPDATE k

 SET    ÅrStartdato = d.minDato, ÅrSlutdato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT År, minDato = MIN(Dato), maxDato = MAX(Dato)

    FROM dbo.Kalender

    GROUP BY År

   ) d ON d.År = k.År

 

 -- UgeSidsteHverdagDato.

 

 UPDATE k

 SET    UgeSidsteHverdagDato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrUge, maxDato = MAX(Dato)

    FROM dbo.Kalender

    WHERE ErHverdag = 1

    GROUP BY ÅrUge

   ) d ON d.ÅrUge = k.ÅrUge

 

 -- MånedSidsteHverdagDato.

 

 UPDATE k

 SET    MånedSidsteHverdagDato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrMåned, maxDato = MAX(Dato)

    FROM dbo.Kalender

    WHERE ErHverdag = 1

    GROUP BY ÅrMåned

   ) d ON d.ÅrMåned = k.ÅrMåned

 

  -- KvartalSidsteHverdagDato.

 

 UPDATE k

 SET    KvartalSidsteHverdagDato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrKvartal, maxDato = MAX(Dato)

    FROM dbo.Kalender

    WHERE ErHverdag = 1

    GROUP BY ÅrKvartal

   ) d ON d.ÅrKvartal = k.ÅrKvartal

 

 -- HalvårSidsteHverdagDato.

 

 UPDATE k

 SET    HalvårSidsteHverdagDato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrHalvår, maxDato = MAX(Dato)

    FROM dbo.Kalender

    WHERE ErHverdag = 1

    GROUP BY ÅrHalvår

   ) d ON d.ÅrHalvår = k.ÅrHalvår

      

 -- ÅrSidsteHverdagDato.

 

 UPDATE k

 SET    ÅrSidsteHverdagDato = d.maxDato

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT År, maxDato = MAX(Dato)

    FROM dbo.Kalender

    WHERE ErHverdag = 1

    GROUP BY År

   ) d ON d.År = k.År

 

 -- ErFørsteMåned og ErSidsteMåned

 

 UPDATE k

 SET    ErFørsteMåned = 1

 FROM   dbo.Kalender k

 WHERE  MånedStartdato = Dato

 

 UPDATE k

 SET    ErSidsteMåned = 1

 FROM   dbo.Kalender k

 WHERE  MånedSlutdato = Dato

 

 -- ErFørsteKvartal og ErSidsteKvartal

 

 UPDATE k

 SET    ErFørsteKvartal = 1

 FROM   dbo.Kalender k

 WHERE  KvartalStartdato = Dato

 

 UPDATE k

 SET    ErSidsteKvartal = 1

 FROM   dbo.Kalender k

 WHERE  KvartalSlutdato = Dato

 

 -- DagIUge0, DagIMåned0, Uge0, Måned0.

 

 UPDATE k

 SET    DagIUge0      = RIGHT('0' + CAST(DagIUge AS nvarchar(2)),2),

        DagIMåned0    = RIGHT('0' + CAST(DagIMåned AS nvarchar(2)),2),

        Ugenummer0    = RIGHT('0' + CAST(Ugenummer AS nvarchar(2)),2),

        Månedsnummer0 = RIGHT('0' + CAST(Månedsnummer AS nvarchar(2)),2)

 FROM   dbo.Kalender k

 

 -- UgeOver2Måneder.

 

 UPDATE k

 SET    ErUgeOver2Måneder = 1

 FROM   dbo.Kalender k

   INNER JOIN

   (

    SELECT ÅrUge

    FROM dbo.Kalender

    GROUP BY ÅrUge

    HAVING COUNT(DISTINCT ÅrMåned) = 2

   ) u ON u.ÅrUge = k.ÅrUge

 

 -- Sæson.

 

 UPDATE k

 SET    Sæson = CASE WHEN Månedsnummer BETWEEN 1 AND  6

                       THEN CAST(År-1 AS nvarchar(4)) + '/' + CAST(År AS nvarchar(4))

                     WHEN Månedsnummer BETWEEN 7 AND 12

                       THEN CAST(År AS nvarchar(4)) + '/' + CAST(IIF(År=9999,9999,År+1) AS nvarchar(4))

                END

 FROM   dbo.Kalender k

 

 -- DageI.

 

 ;WITH dage AS

 (

  SELECT

   Dato,

   DageIÅr      = COUNT(1) OVER(PARTITION BY År),

   DageIHalvår  = COUNT(1) OVER(PARTITION BY ÅrHalvår),

   DageIKvartal = COUNT(1) OVER(PARTITION BY ÅrKvartal),

   DageIMåned   = COUNT(1) OVER(PARTITION BY ÅrMåned)

  FROM dbo.Kalender

 )

 UPDATE k

 SET    DageIÅr      = dage.DageIÅr,

        DageIHalvår  = dage.DageIHalvår,

        DageIKvartal = dage.DageIKvartal,

        DageIMåned   = dage.DageIMåned

 FROM   dbo.Kalender k

   INNER JOIN dage ON dage.Dato = k.Dato

 

/*

Joakim Dalby opfindelse:

ÅrUgeDagIUge til at sammenligne uge 7 i 2016 med uge 7 i 2015 uden at anvende datoer, fordi ugens datoer er forskellige år til år i forhold til datoer i en måned som altid er det samme.

Derfor skal fact tabellen have ÅrUgeDagIUge og der skal laves en ny dimension Uge med et hierarki År -> ArUge -> ÅrUgeDagIUge.

Det kan også være, at man ikke ønsker årstallet, så er UgeDagIUge svaret hvor 11 = uge 1 mandag, 12 = uge 1 tirsdag, 73 = uge 7 onsdag, 407 = uge 40 søndag og 537 uge 53 søndag, d.v.s. 371 muligheder fordi intet årstal.

De 371 kan være i en dimension kaldet Visningsuge med attributten visningsdag: Uge 1 mandag og med et hierarki og anden attribut visningsuge: Uge 1 til at filtrere visningdage med.

En anden dimension kaldet År fordeler data på de forskellige år og sammen med Visningsdag kan man sammenligne ugerne mellem årene f.eks. uge 7 i år 2015 og 2016 selvom datoerne i disse to uger er meget forskellige.

Eller sammenligne uge 1 i 2015 og i 2016, fordi uge 1 i 2015 starter 29-12-2014 til 04-01-2015 d.v.s. juleferie og uge 1 i 2016 starter 04-01-2016 til 10-01-2016 d.v.s. en arbejdsuge.

Uge 7 i 2010 er fra 2010-02-15 til 2010-02-21 og uge 7 i 2019 er fra 2019-02-11 til 2019-02-17 har begge perioder samme værdi i feltet UgeDagIUge fra 71 til 77 så de to perioder kan sammenlignes via feltet UgeDagIUge.

*/

 

 UPDATE k

 SET    ÅrUgeDagIUge = (ÅrUge * 10) + DagIUge,

        UgeDagIUge   = (Ugenummer * 10) + DagIUge

 FROM   dbo.Kalender k

 

/*

Leap week calendar: Hvor eksempelvis mandag 29-12-2014 og mandag 04-01-2016 har samme HelUgePrÅr_DagIÅr = 1. Med et helt antal uger hvert år, og med hvert år på samme ugedag. intercalary week or leap week.

Uge 7 i 2010 er fra 2010-02-15 til 2010-02-21 og uge 7 i 2019 er fra 2019-02-11 til 2019-02-17 har begge perioder samme værdi i feltet HelUgePrÅr_DagIÅr fra 43 til 49 så de to perioder kan sammenlignes via feltet HelUgePrÅr_DagIÅr.

Følgende datoer søndage 2005-01-02, 2010-01-03, 2016-01-03, 2021-01-03, 2027-01-03 har feltet HelUgePrÅr_DagIÅr = 371 og forskellige år i feltet HelUgePrÅr_År har et årstal et år mindre end datoen. Feltet UgeDagIUge = 537 derfor er søndagene i samme uge = 53.

https://en.wikipedia.org/wiki/Leap_week_calendar

https://en.wikipedia.org/wiki/ISO_week_date

*/

 

 UPDATE k

 SET    HelUgePrÅr_DagIÅr = (DATEPART(iso_week, Dato) - 1) * 7 + DATEPART(weekday, Dato),

        HelUgePrÅr_År     = YEAR(DATEADD(day, 26 - DATEPART(iso_week,Dato), Dato))

 FROM   dbo.Kalender k

 

 -- Arbejdsdagsnummer som et fortløbende nummer på hverdage som hverken er helligdag og lukkedag.

 

 ;WITH datoer AS

 (

  SELECT Dato, Arbejdsdagsnummer = ROW_NUMBER() OVER(ORDER BY Dato)

  FROM   dbo.Kalender

  WHERE  DagIUge BETWEEN 1 AND 5 AND ErHelligdag = 0 AND ErLukkedag = 0

 )

 UPDATE k

 SET    Arbejdsdagsnummer = d.Arbejdsdagsnummer

 FROM   dbo.Kalender k

        INNER JOIN datoer d ON d.Dato = k.Dato

 WHERE  DagIUge BETWEEN 1 AND 5 AND ErHelligdag = 0 AND ErLukkedag = 0

 

 -- Arbejdsdagsnummer gentages som dubletter på datoer i weekender, helligdage og lukkedage,

 -- hvorved Arbejdsdagsnummer altid er udfyldt.

 DECLARE @i int = 0

 WHILE @i < 30

 BEGIN

  ;WITH datoer AS

  (

   SELECT a.Dato, b.Arbejdsdagsnummer

   FROM dbo.Kalender a

        INNER JOIN dbo.Kalender b ON b.Dato = DATEADD(day,-1,a.Dato)

   WHERE a.Arbejdsdagsnummer IS NULL

  )

  UPDATE k

  SET    Arbejdsdagsnummer = d.Arbejdsdagsnummer

  FROM   dbo.Kalender k

         INNER JOIN datoer d ON d.Dato = k.Dato

  WHERE  k.Arbejdsdagsnummer IS NULL

 

  SET @i = @i + 1

 END

 UPDATE k

 SET    Arbejdsdagsnummer = 0

 FROM   dbo.Kalender k

 WHERE  Dato = DATEFROMPARTS(@startÅr, 1, 1)

 /*

  Beregning af en Fristdato ud fra en dato og fire arbejdsdage ved at kalde en scalar-valued function

  SELECT Fristdato = dbo.BeregnetDato('2023-03-31', 4, 'Arbejdsdage')

 */

END

 

 

Funktion til at beregne en dato i forhold til en anden dato og antal kalenderdage eller arbejdsdage:

-- =============================================

-- Author:      Joakim Dalby

-- Create date: 27-08-2023

-- Description: Returnerer en beregnet dato ud fra en dato, et antal dage og en type

--              som er enten 'Kalenderdage' eller 'Arbejdsdage'.

--              SELECT dbo.BeregnetDato('2023-01-01', 90, 'Kalenderdage')

--              SELECT dbo.BeregnetDato('2023-01-01', 90, 'Arbejdsdage')

-- Changelog:

-- =============================================

CREATE FUNCTION [dbo].[BeregnetDato](@Dato date, @AntalDage int, @Type varchar(50))

RETURNS date

AS

BEGIN

 DECLARE @BeregnetDato date = NULL

 

 IF @Type = 'Kalenderdage'

 BEGIN

   SELECT @BeregnetDato = DATEADD(day, @AntalDage, @Dato)

 END

      

 IF @Type = 'Arbejdsdage'

 BEGIN

   SELECT @BeregnetDato = MIN(b.Dato)

   FROM dbo.Kalender a

        INNER JOIN dbo.Kalender b ON b.Arbejdsdagsnummer = (a.Arbejdsdagsnummer + @AntalDage)

   WHERE a.Dato = @Dato

 END

 

 RETURN @BeregnetDato

END

 

Kald af funktionen for at vise, hvor stor forskel 90 dage er fra kalenderdage til arbejdsdage:

 

SELECT dbo.BeregnetDato('2023-01-01', 90, 'Kalenderdage') -- 2023-04-01

SELECT dbo.BeregnetDato('2023-01-01', 90, 'Arbejdsdage')  -- 2023-05-22

 

 

SQL script til tabeloprettelse og dataindhold, og til funktionen og stored proceduren Kalender.txt.

 

SQL script til en klokkeslæt tabel og en stored procedure til udfyldelse Klokkeslæt.txt.

 

Der findes mange andre kalender implementationer på nettet f.eks. hos mssqltips.

 

Skudår og 29. februar

SELECT DateAdd(year, 1,'2024-02-29') = 2025-02-28

SELECT DateAdd(day,365,'2023-02-28') = 2024-02-28

SELECT DateAdd(day,366,'2023-02-28') = 2024-02-29

SELECT DateAdd(day,367,'2023-02-28') = 2024-03-01

SELECT DateAdd(year, 4,'1996-02-29') = 2000-02-29 -- skudår i et århundrede når 400 går op i

SELECT DateAdd(year, 4,'1896-02-29') = 1900-02-28 -- ikke skudår i et århundrede fordi 400 ikke går op i

 

Når en person har fødselsdag den 29-02-1968 hvor gammel er personen på følgende dage:

28-02-2023, 28-02-2024, 29-02-2024, 01-03-2024 og 01-03-2025 ?

Personen kan holde sin fødseldag den 28. februar, når der ikke findes nogen 29. februar, fordi det er praktisk, at personen fortsat tilhører februar måned i statistikker. Hvad nu, hvis personen død den 28. februar, har personen så reelt fyldt år? Personen blev først myndig den 01-03-1986, og derfor giver det mest mening, at personen fylder år enten den 29. februar i skudår eller den 1. marts i ikke-skudår, mens personen fortsat tilhører februar måned jf. personens fødselsdag den 29. februar 1968.

 

Hvorfor er det den 24. februar, som er skuddag i et skudår  jf. gregorianske kalender? I det gamle Rom var der kun 23 dage i februar, som var årets sidste måned jf. Julius Cæsars julianske kalender 45 f.Kr. Derfor gav det god mening, at skuddagen blev lagt her, som den 24. februar.

På ét år er der normalt 365 dage, men hver 4. år er der skudår, hvor man tilføjer en ekstra dag til februar, og dermed får 366 dage, det år.

Et skudår har 366 dage grundet at februar har 29 dage.

Et kalender skudår opstår hver 4. år. Skudår kan udregnes ud fra årstal der kan deles med 4. Ved et århundrede skal årstallet gå op i 100 men må ikke gå op i 400, så 1900 var ikke et skudår men det var 2000.

Skuddag, ekstra dag, som hvert fjerde år med visse undtagelser indskydes i februar, for at kalenderåret kan holde trit med Solen. Jorden bruger ca. 365,2422 dage til et omløb om Solen. Derfor er hvert 100 år ikke skudår, da kalenderen skal bremses en dag. Men hvert 400 år er kalenderen igen bagefter med 26,88 timer ca. 1 dag og derfor er hvert 400 år alligevel skudår. Læs mere