CREATE TABLE Calendrier(
    CalDate date primary key,
    CalDateStr varchar(10) NOT NULL,
    CalYear int NOT NULL,
    CalMonth int NOT NULL,
    CalMonthStr varchar(9) NOT NULL,
    CalTrim int NOT NULL,
    CalDay int NOT NULL,
    CalDW int NOT NULL,
    CalDWstr varchar(8) NOT NULL,
    CalWeek int NOT NULL,
    CalWeekYear int NOT NULL,
    CalWorked bit NOT NULL
)
 
declare @d date
set nocount on
select @d = '20000101'
while @d <= '20301231'
begin
    insert into Calendrier(CalDate, CalDateStr, CalYear, CalMonth, CalMonthStr, CalTrim, CalDay, CalDW, CalDWstr, CalWeek, CalWeekYear, CalWorked)
    select @d as caldate,
        left(CONVERT(varchar,@d,120),10) as CalDateStr,
        DATEPART(year,@d) as CalYear,
        DATEPART(month,@d) as CalMonth,
        case DATEPART(month,@d)
            when 1 then 'Janvier'
            when 2 then 'Février'
            when 3 then 'Mars'
            when 4 then 'Avril'
            when 5 then 'Mai'
            when 6 then 'Juin'
            when 7 then 'Juillet'
            when 8 then 'Aout'
            when 9 then 'Septembre'
            when 10 then 'Octobre'
            when 11 then 'Novembre'
            when 12 then 'Décembre' end as CalMonthStr,
        CEILING(CAST(datepart(month,@d) as float)/3.00) as CalTrim,
        datepart(day,@d) as CalDay,
        DATEPART(dw,@d) as CalDW,
        case DATEPART(dw,@d)
            when 1 then 'Lundi'
            when 2 then 'Mardi'
            when 3 then 'Mercredi'
            when 4 then 'Jeudi'
            when 5 then 'Vendredi'
            when 6 then 'Samedi'
            when 7 then 'Dimanche' end as CalDWstr,
        DATEPART(isowk,@d) as CalWeek,
        case when DATEPART(isowk,@d) = 1 and DATEPART(dy,@d) > 300 then DATEPART(year,@d)+1
            when DATEPART(isowk,@d) >= 52 and DATEPART(dy,@d) < 10 then DATEPART(year,@d)-1
            else DATEPART(year,@d) end as CalWeekYear,
        case when DATEPART(dw,@d) between 1 and 5 then 1 else 0 end as CalWorked
    select @d = DATEADD(day,1,@d)
end
set nocount off


update calendrier set calworked = 0 where caldate = '20030101'
update calendrier set calworked = 0 where caldate = '20030421'
update calendrier set calworked = 0 where caldate = '20030501'
update calendrier set calworked = 0 where caldate = '20030508'
update calendrier set calworked = 0 where caldate = '20030529'
update calendrier set calworked = 0 where caldate = '20030609'
update calendrier set calworked = 0 where caldate = '20030714'
update calendrier set calworked = 0 where caldate = '20030815'
update calendrier set calworked = 0 where caldate = '20031111'
update calendrier set calworked = 0 where caldate = '20031225'
update calendrier set calworked = 0 where caldate = '20040101'
update calendrier set calworked = 0 where caldate = '20040412'
update calendrier set calworked = 0 where caldate = '20040520'
update calendrier set calworked = 0 where caldate = '20040531'
update calendrier set calworked = 0 where caldate = '20040714'
update calendrier set calworked = 0 where caldate = '20041101'
update calendrier set calworked = 0 where caldate = '20041111'
update calendrier set calworked = 0 where caldate = '20050505'
update calendrier set calworked = 0 where caldate = '20050516'
update calendrier set calworked = 0 where caldate = '20050714'
update calendrier set calworked = 0 where caldate = '20050815'
update calendrier set calworked = 0 where caldate = '20051101'
update calendrier set calworked = 0 where caldate = '20051111'
update calendrier set calworked = 0 where caldate = '20060417'
update calendrier set calworked = 0 where caldate = '20060501'
update calendrier set calworked = 0 where caldate = '20060508'
update calendrier set calworked = 0 where caldate = '20060525'
update calendrier set calworked = 0 where caldate = '20060605'
update calendrier set calworked = 0 where caldate = '20060714'
update calendrier set calworked = 0 where caldate = '20060815'
update calendrier set calworked = 0 where caldate = '20061101'
update calendrier set calworked = 0 where caldate = '20061225'
update calendrier set calworked = 0 where caldate = '20070101'
update calendrier set calworked = 0 where caldate = '20070409'
update calendrier set calworked = 0 where caldate = '20070501'
update calendrier set calworked = 0 where caldate = '20070508'
update calendrier set calworked = 0 where caldate = '20070517'
update calendrier set calworked = 0 where caldate = '20070815'
update calendrier set calworked = 0 where caldate = '20071101'
update calendrier set calworked = 0 where caldate = '20071225'
update calendrier set calworked = 0 where caldate = '20080101'
update calendrier set calworked = 0 where caldate = '20080324'
update calendrier set calworked = 0 where caldate = '20080501'
update calendrier set calworked = 0 where caldate = '20080508'
update calendrier set calworked = 0 where caldate = '20080714'
update calendrier set calworked = 0 where caldate = '20080815'
update calendrier set calworked = 0 where caldate = '20081111'
update calendrier set calworked = 0 where caldate = '20081225'
update calendrier set calworked = 0 where caldate = '20090101'
update calendrier set calworked = 0 where caldate = '20090413'
update calendrier set calworked = 0 where caldate = '20090501'
update calendrier set calworked = 0 where caldate = '20090508'
update calendrier set calworked = 0 where caldate = '20090521'
update calendrier set calworked = 0 where caldate = '20090601'
update calendrier set calworked = 0 where caldate = '20090714'
update calendrier set calworked = 0 where caldate = '20091111'
update calendrier set calworked = 0 where caldate = '20091225'
update calendrier set calworked = 0 where caldate = '20100101'
update calendrier set calworked = 0 where caldate = '20100405'
update calendrier set calworked = 0 where caldate = '20100513'
update calendrier set calworked = 0 where caldate = '20100714'
update calendrier set calworked = 0 where caldate = '20101101'
update calendrier set calworked = 0 where caldate = '20101111'
update calendrier set calworked = 0 where caldate = '20110425'
update calendrier set calworked = 0 where caldate = '20110602'
update calendrier set calworked = 0 where caldate = '20110714'
update calendrier set calworked = 0 where caldate = '20110815'
update calendrier set calworked = 0 where caldate = '20111101'
update calendrier set calworked = 0 where caldate = '20111111'
update calendrier set calworked = 0 where caldate = '20120409'
update calendrier set calworked = 0 where caldate = '20120501'
update calendrier set calworked = 0 where caldate = '20120508'
update calendrier set calworked = 0 where caldate = '20120517'
update calendrier set calworked = 0 where caldate = '20120528'
update calendrier set calworked = 0 where caldate = '20120815'
update calendrier set calworked = 0 where caldate = '20121101'
update calendrier set calworked = 0 where caldate = '20121225'
update calendrier set calworked = 0 where caldate = '20130101'
update calendrier set calworked = 0 where caldate = '20130401'
update calendrier set calworked = 0 where caldate = '20130501'
update calendrier set calworked = 0 where caldate = '20130508'
update calendrier set calworked = 0 where caldate = '20130509'
update calendrier set calworked = 0 where caldate = '20130520'
update calendrier set calworked = 0 where caldate = '20130815'
update calendrier set calworked = 0 where caldate = '20131101'
update calendrier set calworked = 0 where caldate = '20131111'
update calendrier set calworked = 0 where caldate = '20131225'