DECLARE @Case TABLE (Case_ID INT, Assignment_Date DATETIME, Queue_ID INT)

INSERT @Case
SELECT 2677490, '2008-04-02 16:15:53.480', 1661
UNION ALL SELECT 2677490, '2008-04-03 09:14:20.690', 1062
UNION ALL SELECT 2678373, '2008-04-02 16:38:06.153', 1947
UNION ALL SELECT 2678373, '2008-04-02 16:40:08.027', 1656
UNION ALL SELECT 2678373, '2008-04-03 09:36:56.923', 1083
UNION ALL SELECT 2678386, '2008-04-02 16:38:25.777', 68064
UNION ALL SELECT 2678386, '2008-04-02 16:39:56.277', 1661
UNION ALL SELECT 2678386, '2008-04-03 09:05:15.940', 1354
UNION ALL SELECT 2678386, '2008-04-03 09:37:08.500', 601
UNION ALL SELECT 2679229, '2008-04-02 17:05:24.653', 101
UNION ALL SELECT 2679229, '2008-04-02 17:05:25.997', 1656


SELECT
	Case_ID,
	Assignment_Date,
	Queue_ID
FROM
	(
		SELECT 
			*,
			(
				SELECT 
					MIN(Assignment_Date) 
				FROM 
					@Case AS T 
				WHERE 
					T.Case_ID = A.Case_ID 
					AND T.Assignment_Date > A.Assignment_Date
			) AS NextDate
		FROM
			@Case AS A
	) AS Temp
WHERE
	CAST('2008-04-03 07:00:00' AS DATETIME) BETWEEN Assignment_Date AND NextDate
	AND Queue_ID IN (1656, 1661)

WHERE
	CAST('2008-04-03 07:00:00' AS DATETIME) BETWEEN Assignment_Date AND COALESCE(NextDate, CAST('99991231' AS DATETIME))
	AND Queue_ID IN (1656, 1661)

quote:

---

Originally posted by singularity

spirit, that's not exactly what I'm looking for. I need cases that were still assigned to queues 1656 and 1661 as of 7AM, meaning the next entry on the log is after 7AM.

For example, the case below would need to be captured because it was still in queue 1661 as of 7AM on 4/3 (it did not leave that queue until 9:14am)

2677490 2008-04-02 16:15:53.480 1661
2677490 2008-04-03 09:14:20.690 1062

---