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
|
WITH LaTable(date_debut, date_fin, lieu) AS (
select 1, 3, 'lieu1'
union all select 5, 10, 'lieu1'
union all select 12, 14, 'lieu2'
union all select 16, 18, 'lieu1'
),
Rupture AS (
SELECT
date_debut
, date_fin
, lieu
, CASE WHEN LAG(lieu) OVER(ORDER BY date_debut) = lieu THEN 0 ELSE 1 END AS new
FROM LaTable
),
Groupage AS (
SELECT
date_debut
, date_fin
, lieu
, SUM(new) OVER(ORDER BY date_debut) AS Groupe
FROM rupture
)
SELECT
MIN(date_debut) as debut
, MAX(date_fin) as date_fin
, lieu
FROM Groupage
GROUP BY
lieu
, groupe |
Partager