/*Populating calendar PERIODES TABLE*/
select year( min(EVE_DATE_CREATION)) from <PREFIXE_DW>EVENEMENTS
select year( max(EVE_DATE_CREATION)) from <PREFIXE_DW>EVENEMENTS
DECLARE @StartDate  date = '20000101';
DECLARE @CutoffDate date = DATEADD(year, 10, DATEADD(YEAR, 30, @StartDate));
set @StartDate= (select dateadd(year,-5, min(EVE_DATE_CREATION)) from <PREFIXE_DW>EVENEMENTS)
set @CutoffDate= (select dateadd(year,5, max(EVE_DATE_CREATION)) from <PREFIXE_DW>EVENEMENTS)
;WITH seq(n) AS 
(
  SELECT 0 UNION ALL SELECT n + 1 FROM seq
  WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS 
(
  SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
  SELECT
	PER_DATEC=CONVERT(varchar, d,112),
    PER_DATE         = CONVERT(date, d),
    PER_JOUR          = DATEPART(DAY,       d),
    TheDayName      = DATENAME(WEEKDAY,   d),
    PER_SEMAINE         = DATEPART(WEEK,      d),
    TheISOWeek      = DATEPART(ISO_WEEK,  d),
    TheDayOfWeek    = DATEPART(WEEKDAY,   d),
    PER_MOIS        = DATEPART(MONTH,     d),
    TheMonthName    = DATENAME(MONTH,     d),
    PER_TRIMESTRE      = DATEPART(Quarter,   d),
	PER_SEMESTRE =case WHEN DATEPART(Quarter,   d)<=2 THEN '1' else 2 end,
    PER_ANNEE         = DATEPART(YEAR,      d),
    TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
    TheLastOfYear   = DATEFROMPARTS(YEAR(d), 12, 31),
    TheDayOfYear    = DATEPART(DAYOFYEAR, d)
  FROM d
)
insert into <PREFIXE_DW>PERIODES (PER_DATE,PER_ANNEE,PER_MOIS,PER_JOUR,PER_TRIMESTRE,PER_SEMAINE,PER_SEMESTRE,PER_DATEC)
SELECT s.PER_DATE,s.PER_ANNEE,s.PER_MOIS,s.PER_JOUR,s.PER_TRIMESTRE,s.PER_SEMAINE,s.PER_SEMESTRE,s.PER_DATEC FROM src s
	left outer join <PREFIXE_DW>PERIODES p on s.PER_DATE=p.PER_DATE
	where p.PER_DATE is null
  ORDER BY s.PER_DATE
  OPTION (MAXRECURSION 0);

<SEPARATEUR>
/*MAJ PER_CODE_PERIODE*/
UPDATE dwp set dwp.PER_CODE_PERIODE=hap.CODE_PER,dwp.PER_DATE_DEBUT_PERIODE=DATE_DEB ,dwp.PER_DATE_FIN_PERIODE=DATE_FIN
	from <PREFIXE_DW>PERIODES dwp, <PREFIXE_HA>PERIODES hap where dwp.PER_DATE >=DATE_DEB and dwp.PER_DATE<=DATE_FIN