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
| CREATE TABLE SAV
(NUM_APPEL INT,
ETAT INT, JOUR DATETIME)
SET DATEFORMAT mdy
INSERT INTO SAV VALUES (1234, 5, '14/06/2007')
INSERT INTO SAV VALUES (1234, 6, '17/06/2007')
INSERT INTO SAV VALUES (1234, 7, '19/06/2007')
INSERT INTO SAV VALUES (6684, 3, '26/06/2007')
INSERT INTO SAV VALUES (6684, 4, '30/06/2007')
SELECT T1.NUM_APPEL, T1.ETAT, T1.JOUR AS DEBUT,
COALESCE(MIN(T2.JOUR), '20991231') AS FIN
FROM SAV T1
LEFT OUTER JOIN SAV T2
ON T1.NUM_APPEL = T2.NUM_APPEL
AND T1.JOUR < T2.JOUR
GROUP BY T1.NUM_APPEL, T1.ETAT, T1.JOUR
NUM_APPEL ETAT DEBUT FIN
----------- ----------- --------------------------- --------------------------
1234 5 2007-06-14 00:00:00.000 2007-06-17 00:00:00.000
1234 6 2007-06-17 00:00:00.000 2007-06-19 00:00:00.000
1234 7 2007-06-19 00:00:00.000 2099-12-31 00:00:00.000
6684 3 2007-06-26 00:00:00.000 2007-06-30 00:00:00.000
6684 4 2007-06-30 00:00:00.000 2099-12-31 00:00:00.000 |
Partager