SQL Server Performance Forum – Threads Archive

Help with an SQL query

Hi. I am trying to summarize some data based on datetime values, and I am having some trouble. Here is an example of some of the data I am trying to summarize:
ID ORDER_NO USER OPEN DATE CLOSE DATE
— ——– —— ———————– ———————–
10 34061354 3397 2003-04-14 09:57:30.173 2003-04-14 09:58:25.233
11 34061354 3397 2003-04-14 09:58:25.267 2003-04-14 09:58:34.203
12 34061646 3397 2003-04-14 09:58:37.017 2003-04-14 09:59:00.437 Basically this is a log of when an order has been opened and closed. The problem is that I need to write a query that will look at ID 10 and 11 and see that the CLOSE DATE of 10 is within 1 second of the OPEN DATE of 11 and return only one record that looks like: ORDER_NO USER OPEN DATE CLOSE DATE
——– —— ———————– ———————–
34061354 3397 2003-04-14 09:57:30.173 2003-04-14 09:58:34.203 If the records are off by more than a second, I do not want to combine them into one result set. I hope that I am making sense. Any help will be appreciated. Thanks,
Jan

---

Read this excellent article by SQL Server MVP Itzik Ben-Gan: http://www.sqlmag.com/Articles/Index.cfm?ArticleID=23511 It is about a very similar problem, and it wouldn’t be too difficult to
adapt his solutions to your problem.

---

declare @orders table ([ID] int, [ORDER_NO] int, [USER] int, [OPEN DATE] datetime, [CLOSE DATE] datetime) INSERT INTO @orders ([ID] , [ORDER_NO] , [USER] , [OPEN DATE], [CLOSE DATE] )
SELECT 10, 34061354, 3397, ‘2003-04-14 09:57:30.173′ ,’2003-04-14 09:58:25.233’
UNION ALL
SELECT 11, 34061354, 3397, ‘2003-04-14 09:58:25.267’, ‘2003-04-14 09:58:34.203’
UNION ALL
SELECT 12, 34061646, 3397, ‘2003-04-14 09:58:37.017’, ‘2003-04-14 09:59:00.437’ SELECT t1.ID, t1.[CLOSE DATE], t2.[OPEN DATE], DATEDIFF(second, t1.[CLOSE DATE], t2.[OPEN DATE])
FROM @orders t1
INNER JOIN @orders t2
ON t1.ID < t2.ID
WHERE DATEDIFF(second, t1.[CLOSE DATE], t2.[OPEN DATE]) <= 1 Bambola.

---

]]>