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
| CREATE TABLE T_PERIODE_PRD
(PRD_ID INT NOT NULL PRIMARY KEY,
PRD_DEBUT DATETIME NOT NULL,
PRD_FIN DATETIME NOT NULL,
CONSTRAINT CK_PRD_COHERENCE CHECK (PRD_FIN > PRD_DEBUT))
GO
CREATE TABLE T_JOUR_JOR
(JOR_DATE DATETIME NOT NULL PRIMARY KEY,
JOR_FERIE BIT NOT NULL DEFAULT 0)
GO
-- jeu d'essais :
INSERT INTO T_PERIODE_PRD VALUES (1, '20071218', '20071225')
INSERT INTO T_PERIODE_PRD VALUES (2, '20071219', '20080101')
INSERT INTO T_PERIODE_PRD VALUES (3, '20071220', '20080105')
DECLARE @J DATETIME
SET @J = '20071201'
WHILE @J < '20080131'
BEGIN
INSERT INTO T_JOUR_JOR (JOR_DATE) VALUES (@J)
SET @J = @J + 1
END
GO
UPDATE T_JOUR_JOR
SET JOR_FERIE = 1
WHERE JOR_DATE IN ('20071225', '20080101')
GO
-- requête :
SELECT PRD_ID, PRD_DEBUT, PRD_FIN,
MIN(JOR_DATE) AS PRD_FIN_FERIE,
DATEDIFF(d,PRD_DEBUT, MIN(JOR_DATE)) AS PRD_DUREE_JOUR
FROM T_PERIODE_PRD
INNER JOIN T_JOUR_JOR
ON PRD_FIN <= JOR_DATE
WHERE JOR_FERIE = 0
GROUP BY PRD_ID, PRD_DEBUT, PRD_FIN
PRD_ID PRD_DEBUT PRD_FIN PRD_FIN_FERIE PRD_DUREE_JOUR
----------- ----------- ----------- ------------- --------------
1 2007-12-18 2007-12-25 2007-12-26 8
2 2007-12-19 2008-01-01 2008-01-02 14
3 2007-12-20 2008-01-05 2008-01-05 16
-- raffinement : si vous voulez en sus gérer les jours ouvrables, modifiez comme suit :
ALTER TABLE T_JOUR_JOR
ADD JOR_OUVRABLE INT NOT NULL DEFAULT 1
GO
UPDATE T_JOUR_JOR
SET JOR_OUVRABLE = 0
WHERE DATEPART(dw, JOR_DATE) IN (6 ,7)
GO
-- requête :
SELECT PRD_ID, PRD_DEBUT, PRD_FIN,
MIN(JOR_DATE) AS PRD_FIN_FERIE,
(SELECT SUM(JOR_OUVRABLE)
FROM T_JOUR_JOR
WHERE JOR_DATE BETWEEN PRD.PRD_DEBUT
AND MIN(JOR.JOR_DATE)) AS PRD_DUREE_JOUR_OUVRES
FROM T_PERIODE_PRD AS PRD
INNER JOIN T_JOUR_JOR AS JOR
ON PRD_FIN <= JOR_DATE
WHERE JOR_FERIE = 0
AND JOR_OUVRABLE = 1
GROUP BY PRD_ID, PRD_DEBUT, PRD_FIN
PRD_ID PRD_DEBUT PRD_FIN PRD_FIN_FERIE PRD_DUREE_JOUR_OUVRES
----------- ----------- ----------- ------------- ---------------------
1 2007-12-18 2007-12-25 2007-12-26 7
2 2007-12-19 2008-01-01 2008-01-02 11
3 2007-12-20 2008-01-05 2008-01-07 13 |
Partager