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
|
;WITH tmp AS(
SELECT
'du '
+ CONVERT(CHAR(10), debut.dte , 103)
+ ' au '
+ CONVERT(CHAR(10), MIN(fin.dte), 103) as periode
,
RANK() OVER (ORDER BY debut.dte) AS Rang
FROM cte debut
LEFT JOIN cte CDebut
ON DATEADD(DAY, 1, CDebut.dte) = debut.dte
INNER JOIN cte fin
ON fin.dte > debut.dte
LEFT JOIN cte CFin
ON DATEADD(DAY, -1, Cfin.dte) = fin.dte
WHERE CDebut.dte IS NULL
AND CFin.dte IS NULL
GROUP BY debut.dte
),
res as (
SELECT rang, CAST(periode AS VARCHAR(MAX)) as periode
FROM tmp
WHERE Rang = 1
UNION ALL
SELECT tmp.rang, res.periode + ', ' + tmp.periode
FROM tmp
INNER JOIN res
ON tmp.rang = res.rang + 1
)
SELECT MAX(periode)
FROM res |
Partager