1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210
|
/*
drop view jours_travailles;
drop procedure CreerSamediDimanche;
drop procedure CreerJours;
drop table jour;
drop table calendrier;
*/
-- Table de définition des calendriers
create table calendrier
(
cal_id int identity(1,1) not null primary key,
cal_nom varchar(30) not null
);
-- Table des jours : détail de chaque calendrier
create table jour
(
jou_id int identity(1,1) not null primary key,
cal_id int not null references calendrier(cal_id),
jou_date date not null,
jou_annee as year(jou_date)
);
-- Index permettant d'optimiser les accès à la table des jours
create index ix_jour_annee on jour(cal_id, jou_annee);
create unique index ix_jour_jour on jour(cal_id, jou_annee);
go
-- Vue permettant d'obtenir pour chaque calendrier le nombre de jours travaillés
create view jours_travailles
as
select tous.jou_annee, calendrier.cal_id, tous.jou_date
from jour tous
inner join calendrier on calendrier.cal_id > 2
left outer join jour dim on dim.cal_id = 2 and dim.jou_date = tous.jou_date
left outer join jour fer on fer.cal_id = calendrier.cal_id and fer.jou_date = tous.jou_date
where tous.cal_id = 1
and dim.jou_id is null
and fer.jou_id is null;
go
-- Création de tous les jours de l'année dans un calendrier
create procedure CreerJours(@cal_id as int, @annee as int)
as
begin
declare @date as date;
declare @datemax as date;
-- On vérifie que le calendrier existes...
if not exists (select null from calendrier where cal_id = @cal_id)
begin
raiserror('Calendrier inexistant', 1, 1);
return;
end;
-- On vérifie qu'il n'y a pas déjà de jour dans le calendrier
if exists (select null from jour where cal_id = @cal_id and jou_annee = @annee)
begin
raiserror('Des jours existent déjà dans ce calendier pour cette année', 1, 1);
return;
end;
-- Ok, on peut créer les jours
set @date = cast(cast(@annee as CHAR(4)) + '-01-01' as date);
set @datemax = dateadd(year, 1, @date);
while @date < @datemax
begin
insert into jour (cal_id, jou_date) values (@cal_id, @date);
set @date = dateadd(day, 1, @date);
end;
end;
go
-- Création des week-end dans un calendrier
create procedure CreerSamediDimanche(@cal_id as int, @annee as int)
as
begin
declare @date as date;
declare @datemax as date;
-- On vérifie que le calendrier existes...
if not exists (select null from calendrier where cal_id = @cal_id)
begin
raiserror('Calendrier inexistant', 1, 1);
return;
end;
-- On vérifie qu'il n'y a pas déjà de jour dans le calendrier
if exists (select null from jour where cal_id = @cal_id and jou_annee = @annee)
begin
raiserror('Des jours existent déjà dans ce calendier pour cette année', 1, 1);
return;
end;
-- Ok, on peut créer les jours
set @date = cast(cast(@annee as CHAR(4)) + '-01-01' as date);
set @datemax = dateadd(year, 1, @date);
while @date < @datemax
begin
if DATEPART(weekday, @date) in (1, 7)
begin
insert into jour (cal_id, jou_date) values (@cal_id, @date);
end;
set @date = dateadd(day, 1, @date);
end;
end;
go
-- Création des calendriers
insert into calendrier (cal_nom) values ('Tous les jours');
insert into calendrier (cal_nom) values ('Samedis et dimanches');
insert into calendrier (cal_nom) values ('Fériés français');
insert into calendrier (cal_nom) values ('Fériés anglais');
insert into calendrier (cal_nom) values ('Fériés écossais');
-- Alimentation du calendrier de référence des jours de l'année
exec CreerJours 1, 2012;
exec CreerJours 1, 2013;
-- Alimentation du calendrier de référence des week-end de l'année
exec CreerSamediDimanche 2, 2012;
exec CreerSamediDimanche 2, 2013;
-- Fériés français
insert into jour (cal_id, jou_date) values (3, '2012-01-01');
insert into jour (cal_id, jou_date) values (3, '2012-04-09');
insert into jour (cal_id, jou_date) values (3, '2012-05-01');
insert into jour (cal_id, jou_date) values (3, '2012-05-08');
insert into jour (cal_id, jou_date) values (3, '2012-05-17');
insert into jour (cal_id, jou_date) values (3, '2012-05-28');
insert into jour (cal_id, jou_date) values (3, '2012-07-14');
insert into jour (cal_id, jou_date) values (3, '2012-08-15');
insert into jour (cal_id, jou_date) values (3, '2012-11-01');
insert into jour (cal_id, jou_date) values (3, '2012-11-11');
insert into jour (cal_id, jou_date) values (3, '2012-12-25');
insert into jour (cal_id, jou_date) values (3, '2013-01-01');
insert into jour (cal_id, jou_date) values (3, '2012-04-01');
insert into jour (cal_id, jou_date) values (3, '2013-05-01');
insert into jour (cal_id, jou_date) values (3, '2013-05-08');
insert into jour (cal_id, jou_date) values (3, '2013-05-09');
insert into jour (cal_id, jou_date) values (3, '2013-05-20');
insert into jour (cal_id, jou_date) values (3, '2013-07-14');
insert into jour (cal_id, jou_date) values (3, '2013-08-15');
insert into jour (cal_id, jou_date) values (3, '2013-11-11');
insert into jour (cal_id, jou_date) values (3, '2013-11-01');
insert into jour (cal_id, jou_date) values (3, '2013-12-25');
-- Fériés anglais
insert into jour (cal_id, jou_date) values (4, '2012-01-01');
insert into jour (cal_id, jou_date) values (4, '2012-04-06');
insert into jour (cal_id, jou_date) values (4, '2012-04-09');
insert into jour (cal_id, jou_date) values (4, '2012-05-07');
insert into jour (cal_id, jou_date) values (4, '2012-05-28');
insert into jour (cal_id, jou_date) values (4, '2012-08-27');
insert into jour (cal_id, jou_date) values (4, '2012-12-25');
insert into jour (cal_id, jou_date) values (4, '2012-12-26');
insert into jour (cal_id, jou_date) values (4, '2013-01-01');
insert into jour (cal_id, jou_date) values (4, '2013-03-29');
insert into jour (cal_id, jou_date) values (4, '2013-04-01');
insert into jour (cal_id, jou_date) values (4, '2013-05-06');
insert into jour (cal_id, jou_date) values (4, '2013-05-27');
insert into jour (cal_id, jou_date) values (4, '2013-08-26');
insert into jour (cal_id, jou_date) values (4, '2013-12-25');
insert into jour (cal_id, jou_date) values (4, '2013-12-26');
-- Fériés écossais
insert into jour (cal_id, jou_date) values (5, '2012-01-01');
insert into jour (cal_id, jou_date) values (5, '2012-01-02');
insert into jour (cal_id, jou_date) values (5, '2012-04-06');
insert into jour (cal_id, jou_date) values (5, '2012-05-07');
insert into jour (cal_id, jou_date) values (5, '2012-05-28');
insert into jour (cal_id, jou_date) values (5, '2012-08-06');
insert into jour (cal_id, jou_date) values (5, '2012-11-30');
insert into jour (cal_id, jou_date) values (5, '2012-12-25');
insert into jour (cal_id, jou_date) values (5, '2012-12-26');
insert into jour (cal_id, jou_date) values (5, '2013-01-01');
insert into jour (cal_id, jou_date) values (5, '2013-01-02');
insert into jour (cal_id, jou_date) values (5, '2013-03-29');
insert into jour (cal_id, jou_date) values (5, '2013-05-06');
insert into jour (cal_id, jou_date) values (5, '2013-05-27');
insert into jour (cal_id, jou_date) values (5, '2013-08-05');
insert into jour (cal_id, jou_date) values (5, '2013-11-30');
insert into jour (cal_id, jou_date) values (5, '2013-12-25');
insert into jour (cal_id, jou_date) values (5, '2013-12-26');
-- On regarde parmi les 7 premiers jours de mai lesquels sont effectivement travaillés dans le calendrier français
with test as
(
select cast('2012-05-01' as date) madate
union all
select cast('2012-05-02' as date) madate
union all
select cast('2012-05-03' as date) madate
union all
select cast('2012-05-04' as date) madate
union all
select cast('2012-05-05' as date) madate
union all
select cast('2012-05-06' as date) madate
union all
select cast('2012-05-07' as date) madate
)
select test.madate
from test
inner join jours_travailles jt on jt.jou_date = test.madate
where jt.cal_id = 3; |
Partager