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
| WITH A (Col1, Col2) AS
(
SELECT 'Event_A', CAST('01/01/2009' AS SMALLDATETIME) UNION ALL
SELECT 'Event_A', CAST('02/01/2009' AS SMALLDATETIME) UNION ALL
SELECT 'Event_A', CAST('03/01/2009' AS SMALLDATETIME) UNION ALL
SELECT 'Event_B', CAST('04/01/2009' AS SMALLDATETIME) UNION ALL
SELECT 'Event_B', CAST('05/01/2009' AS SMALLDATETIME) UNION ALL
SELECT 'Event_A', CAST('06/01/2009' AS SMALLDATETIME) UNION ALL
SELECT 'Event_A', CAST('07/01/2009' AS SMALLDATETIME)
), A_Id (Col1, Col2, G_Id) AS
(
SELECT Col1, Col2,
ROW_NUMBER() OVER( ORDER BY Col2 ASC) -
ROW_NUMBER() OVER(PARTITION BY Col1 ORDER BY Col2 ASC)
FROM A
)
SELECT Col1, MIN(Col2) AS Date_Min, MAX(Col2) AS Date_Max
FROM A_Id
GROUP BY G_Id, Col1
ORDER BY MIN(Col2) ASC;
Col1 Date_Min Date_Max
------- ----------------------- -----------------------
Event_A 01/01/2009 00:00:00 03/01/2009 00:00:00
Event_B 04/01/2009 00:00:00 05/01/2009 00:00:00
Event_A 06/01/2009 00:00:00 07/01/2009 00:00:00 |
Partager