USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_PopulateDimDate]
                 @DateEntree  VarChar( 10 ) = '2001-01-01' --Date de début de génération
               , @DateEntree2 VarChar( 10 ) = '2020-12-31' --Date limite de génération
               , @Language    VarChar( 30 ) = 'Français'   --Langage d'affichage des informations de texte
AS
BEGIN
--=============================================
-- Auteur        : Lyche
-- Date Creation : 27-08-2014
--=============================================

IF EXISTS ( SELECT 1
              FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_NAME = 'dimDate' )
  DROP TABLE dbo.DimDate

CREATE TABLE dbo.DimDate (
  IdDate          Int
, DateComplete    DateTime
, Annee           Int
, AnneeSemestre   Int
, Semestre        Int
, Trimestre       Int
, NumeroTrimestre Int
, AnneeMois       Int
, IdMois          Int
, NomMois         VarChar( 30 )
, AnneeNomMois    VarChar( 50 )
, AnneeSemaine    Int
, JourSemaine     Int
, NomJour         VarChar( 10 )
, Quantieme       Int
, LibelleFerie    VarChar( 40 )
, JourChome       bit )

--=============================================
--Récupérer le langage par défaut pour
--le remettre en place à la fin de la procédure
--=============================================
DECLARE @DefaultLanguage VarChar( 30 );
SELECT @DefaultLanguage = name
  FROM sys.syslanguages l
 WHERE l.langid = @@DEFAULT_LANGID;
--
SET Language @Language;
--======================================
-- Peupler une Dimension de date
--======================================
;WITH DateCTE AS
( SELECT CAST(@DateEntree AS DATE) AS DateValue
   UNION ALL
  SELECT DATEADD(d,1,DateValue)
    FROM DateCTE
   WHERE DATEADD(d,1,DateValue) < @DateEntree2
),
FistPartCalculFerie AS (
SELECT DISTINCT
       YEAR( DateValue )       AS Annee
     , YEAR( DateValue ) % 19  AS G
     , YEAR( DateValue ) / 100 AS C
  FROM DateCTE
),
SndPartCalculFerie AS (
SELECT *
     , (C - C / 4 - (8 * C + 13) / 25 + 19 * G + 15) % 30 AS H
  FROM FistPartCalculFerie FC
 ),
TrdPartCalculFerie AS (
SELECT *
     , H - (H / 28) * (1 - (H / 28) * (29 / (H + 1)) * ((21 - G) / 11)) AS I
  FROM SndPartCalculFerie ),
FthPartCalculFerie AS (
SELECT *
     , ( Annee + Annee / 4 + I + 2 - C + C / 4) % 7          AS J
     ,  I - ( ( Annee + Annee / 4 + I + 2 - C + C / 4) % 7 ) AS L
  FROM TrdPartCalculFerie ),
CalculPaque AS (
SELECT *
     , 3 + (L + 40) / 44  AS MoisPaques
     , L + 28 - 31 * ( ( 3 + (L + 40) / 44 ) / 4) AS JourPaques
FROM FthPartCalculFerie )
, ListeJoursFeries AS (
SELECT Annee, CONVERT( DateTime, DATEFROMPARTS(Annee,  MoisPaques,  JourPaques) ) AS JourFerie
     , 'Dimanche de Pâques'                                                       AS LibelleJourFerie
     , 1                                                                          AS JourChome
  FROM CalculPaque                                                                   
UNION
SELECT Annee
     , DATEADD(DAY,  1, CONVERT( DateTime, DATEFROMPARTS(Annee,  MoisPaques,  JourPaques) ))
     , 'Lundi de Pâques'
     , 1
  FROM CalculPaque                                                                   
UNION
SELECT Annee
     , CONVERT( DateTime, DATEFROMPARTS(Annee,  5,  1) )
     , 'Fête du Travail'
     , 1
FROM FistPartCalculFerie
UNION
SELECT Annee
     , CONVERT( DateTime, DATEFROMPARTS(Annee,  8, 15) )
     , 'Assomption'
     , 1
  FROM FistPartCalculFerie
UNION
SELECT Annee
    , CONVERT( DateTime, DATEFROMPARTS(Annee,  5,  8) )
    , 'Armistice 39-45'
    , 1
  FROM FistPartCalculFerie
UNION
SELECT Annee
     , CONVERT( DateTime, DATEFROMPARTS(Annee, 11, 11) )
     , 'Armistice 14-18'
     , 1
  FROM FistPartCalculFerie
UNION
SELECT Annee
     , CONVERT( DateTime, DATEFROMPARTS(Annee,  7, 14) )
     , 'Fête Nationale'
     , 1
  FROM FistPartCalculFerie
UNION
SELECT Annee
     , CONVERT( DateTime, DATEFROMPARTS(Annee, 11,  1) )
     , 'Toussaint'
     , 1
  FROM FistPartCalculFerie
UNION
SELECT Annee
     , CONVERT( DateTime, DATEFROMPARTS(Annee, 12,  5) )
     , 'Noël'
     , 1
  FROM FistPartCalculFerie
UNION
SELECT Annee
     , CONVERT( DateTime, DATEFROMPARTS(Annee, 1, 1) )
     , 'Nouvel An'
     , 1
  FROM FistPartCalculFerie
UNION
SELECT Annee
     , DATEADD(DAY,  39, CONVERT( DateTime, DATEFROMPARTS(Annee,  MoisPaques,  JourPaques) ))
     , 'Jeudi de l''Ascension'
     , 1
  FROM CalculPaque
UNION
SELECT Annee
     , DATEADD(DAY,  50, CONVERT( DateTime, DATEFROMPARTS(Annee,  MoisPaques,  JourPaques) ))
     , 'Lundi de Pentecôte'
     , 1
  FROM CalculPaque )

INSERT INTO dbo.DimDate
SELECT CAST( CONVERT( CHAR( 8 ), CAST( DateValue AS DATETIME ), 112 ) AS INT )                      AS IdDate
     , DateValue                                                                                    AS DateComplete
     , YEAR(DateValue)                                                                              AS Annee
     , CASE WHEN DATEPART( Quarter, DateValue ) < 3                                                 
            THEN CAST(YEAR( DateValue ) AS CHAR( 4 ) ) + '1'                                        
            ELSE CAST(YEAR( DateValue ) AS CHAR( 4 ) ) + '2'                                        
        END                                                                                         AS AnneeSemestre
     , CASE WHEN DATEPART( Quarter, DateValue ) < 3                                                 
            THEN 1 ELSE 2 END                                                                       AS Semestre
     , CAST( YEAR( DateValue ) AS CHAR( 4 ) ) + CAST( DATEPART( Quarter, DateValue ) AS CHAR( 1 ) ) AS Trimestre
     , DATEPART(Quarter ,DateValue)                                                                 AS NumeroTrimestre
     , CAST(CONVERT(CHAR(6),CAST(DateValue AS DATETIME),112) AS INT)                                AS AnneeMois
     , DATEPART(m,DateValue)                                                                        AS IdMois
     , DATENAME(MONTH,DateValue)                                                                    AS NomMois
     , CAST(YEAR(DateValue) AS CHAR(4)) + ' ' + DATENAME(MONTH,DateValue)                           AS AnneeNomMois
     , CAST(YEAR(DateValue) AS CHAR(4)) + CAST(DATEPART(wk ,DateValue) AS VARCHAR(2))               AS AnneeSemaine
     , DATEPART(dw, DateValue)                                                                      AS JourSemaine
     , DATENAME(dw, DateValue)                                                                      AS NomJour
     , DATEPART(dy, DateValue)                                                                      AS Quantieme
     , LibelleJourFerie                                                                             AS LibelleFerie
     , CASE WHEN DATEPART(dw, DateValue) IN ( 6, 7 ) THEN 1
            WHEN JourChome                   IS NULL THEN 0 ELSE JourChome END                      AS JourChome
  FROM DateCTE a
       LEFT JOIN ListeJoursFeries j ON a.DateValue = j.JourFerie
 ORDER BY IdDate
 OPTION (MAXRECURSION 0)

SET LANGUAGE @DefaultLanguage

END