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 33 34 35 36 37 38 39
| DECLARE @T TABLE
(
entityID INT,
dateAppel DATETIME,
dateProchainAppel DATETIME,
userID INT
)
INSERT INTO @T VALUES (53, '20090908 18:29:07.000', '20090911 00:00:00.000', 14)
INSERT INTO @T VALUES (53, '20090917 08:57:36.000', '20090918 00:00:00.000', 14)
INSERT INTO @T VALUES (53, '20090917 16:12:28.000', '20091020 00:00:00.000', 14)
INSERT INTO @T VALUES (53, '20090929 11:12:47.000', '20090929 00:00:00.000', 14)
INSERT INTO @T VALUES (53, '20090929 11:13:50.000', '20091002 00:00:00.000', 14)
INSERT INTO @T VALUES (53, '20091002 15:49:47.000', '20091005 00:00:00.000', 14)
--------------------------------------------------------------------------------
INSERT INTO @T VALUES (53, '20090907 08:29:07.000', '20091004 00:00:00.000', 15)
INSERT INTO @T VALUES (53, '20090916 18:57:36.000', '20090917 00:00:00.000', 15)
INSERT INTO @T VALUES (53, '20090916 06:12:28.000', '20091019 00:00:00.000', 15)
INSERT INTO @T VALUES (53, '20090928 01:12:47.000', '20090928 00:00:00.000', 15)
INSERT INTO @T VALUES (53, '20090928 01:13:50.000', '20091001 00:00:00.000', 15)
INSERT INTO @T VALUES (53, '20091001 05:49:47.000', '20091003 00:00:00.000', 15)
;WITH
CTE_DERNIER_APPEL AS
(
SELECT entityID, userID, MAX(dateAppel) AS maxDateAppel
FROM @T
GROUP BY entityID, userID
)
SELECT A.entityID,
A.userID,
CTE.maxDateAppel AS dateDernierAppel,
MAX(A.dateProchainAppel) AS dateProchainAppel
FROM CTE_DERNIER_APPEL AS CTE
JOIN @T AS A
ON CTE.entityID = A.entityID
AND CTE.userID = A.userID
GROUP BY A.entityID, A.userID, CTE.maxDateAppel
HAVING MAX(A.dateProchainAppel) >= CTE.maxDateAppel |
Partager