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
|
SET DATEFORMAT dmy
CREATE TABLE laTable(
ID int
, type varchar(50)
, dateDebut datetime
, datefin datetime
)
INSERT INTO LaTable VALUES
(1, 'A' , '01-01-2012', '31-01-2012')
,(1, 'B' , '01-02-2012', '31-03-2013')
,(1, 'C' , '05-04-2013', '30-04-2013')
,(2, '0A', '01-01-2010', '31-01-2011')
,(2, 'F' , '01-01-2013', '30-01-2013')
,(2, 'D' , '03/02/2013', '30/04/2013')
,(2, 'G' , '01/05/2013', '30/06/2014')
SELECT ID, MAX(datedebut) as DateDebut
FROM LaTable P
WHERE NOT EXISTS (
SELECT 1
FROM LaTable S
WHERE P.ID = S.ID
AND S.DateFin < P.DateDebut
AND DATEDIFF(DAY, S.DateFin, P.DateDebut) < 30
)
GROUP BY ID |