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
|
DECLARE @t TABLE(
id CHAR(1),
debut DATE,
fin DATE
)
INSERT INTO @t VALUES('x','2008-01-15','2008-01-29')
, ('y', '2008-01-10', '2008-01-18')
,('z','2008-01-25','2008-01-29')
;WITH dte AS(
SELECT debut
FROM @t t1
UNION
SELECT fin
FROM @t
),
dteOK AS (
SELECT dte.debut, ROW_NUMBER() OVER(ORDER BY dte.debut) AS RN
FROM dte
INNER JOIN @t t
ON dte.debut BETWEEN t.debut AND t.fin
WHERE t.id = 'x'
)
SELECT d1.debut AS debut, d2.debut AS fin, d1.RN AS Indice
FROM dteOK d1
INNER JOIN dteOK d2 ON d1.RN = d2.RN |
Partager