I have an order table which has item, order creation and cancelation
times. Canceled order records have cancel time in them, regular orders
have NULL in that field. I need to trace re-orders where the same item
is canceled and within 1 hour it is ordered again.

Here is my setup (SQL Server 2005)

declare @T table (
ID int IDENTITY,
ITEM_ID int NOT NULL,
CREATE_TIME datetime NOT NULL,
CANCEL_TIME datetime NULL
)
insert @T
select 55, '20110606 11:10', '20110606 11:25' union all
select 55, '20110606 11:44', NULL union all
select 10, '20110606 06:00', '20110606 06:05' union all
select 10, '20110606 06:10', '20110606 06:11' union all
select 10, '20110606 06:20', '20110606 06:22' union all
select 10, '20110606 08:30', '20110606 08:33' union all
select 10, '20110606 08:40', NULL

select t1.ITEM_ID
, t1.CREATE_TIME [CR1]
, t2.CREATE_TIME [CR2]
, DATEDIFF(minute, t1.CREATE_TIME, t2.CREATE_TIME) [Diff]
from @T t1,
@T t2
where t1.ITEM_ID=t2.ITEM_ID
and t1.CREATE_TIME < t2.CREATE_TIME
and t1.CANCEL_TIME IS NOT NULL
and DATEDIFF(minute, t1.CREATE_TIME, t2.CREATE_TIME) < 60

Results:
ITEM_ID CR1 CR2 Diff
55 2011-06-06 11:10:00.000 2011-06-06 11:44:00.000 34
10 2011-06-06 06:00:00.000 2011-06-06 06:10:00.000 10
10 2011-06-06 06:00:00.000 2011-06-06 06:20:00.000 20
10 2011-06-06 06:10:00.000 2011-06-06 06:20:00.000 10
10 2011-06-06 08:30:00.000 2011-06-06 08:40:00.000 10

Problem with my code is that item #10 is reported extra time, I need
to somehow say "do join with one canceled before me record only, not
with all previous records that are canceled" but I don't know how.

Expected results would be:

ITEM_ID CR1 CR2 Diff
55 2011-06-06 11:10:00.000 2011-06-06 11:44:00.000 34
10 2011-06-06 06:00:00.000 2011-06-06 06:10:00.000 10
10 2011-06-06 06:10:00.000 2011-06-06 06:20:00.000 10
10 2011-06-06 08:30:00.000 2011-06-06 08:40:00.000 10

Any pointers would be appreciated.

migurus (migurus (AT) yahoo (DOT) com) writes:
Here are two different solutions:

select t1.ITEM_ID, t1.CREATE_TIME [CR1], t2.CREATE_TIME [CR2],
DATEDIFF(minute, t1.CREATE_TIME, t2.CREATE_TIME) [Diff]
from @T t1
CROSS APPLY (SELECT TOP 1 t2.CREATE_TIME
FROM @T t2
WHERE t2.ITEM_ID = t1.ITEM_ID
AND t2.CREATE_TIME > t1.CREATE_TIME
ORDER BY t2.CREATE_TIME) AS t2
WHERE t1.CANCEL_TIME IS NOT NULL
AND DATEDIFF(minute, t1.CREATE_TIME, t2.CREATE_TIME) < 60

;WITH numbered AS (
SELECT ITEM_ID, CREATE_TIME, CANCEL_TIME,
rowno = row_number() OVER(PARTITION BY ITEM_ID
ORDER BY CREATE_TIME)
FROM @T
)
select t1.ITEM_ID, t1.CREATE_TIME [CR1], t2.CREATE_TIME [CR2],
DATEDIFF(minute, t1.CREATE_TIME, t2.CREATE_TIME) [Diff]
FROM numbered t1
JOIN numbered t2 ON t1.ITEM_ID = t2.ITEM_ID
AND t1.rowno +1 = t2.rowno
WHERE t1.CANCEL_TIME IS NOT NULL
AND DATEDIFF(minute, t1.CREATE_TIME, t2.CREATE_TIME) < 60

The first uses the CROSS APPLY operator. APPLY is like JOIN, but accepts
input from the left-side in the table expression on the left side.

The second numbers the rows to sort it out.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On Jun 8, 2:31*pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Thank you very much, these new techniques look interesting.

I'd like to add that I was able to achieve the same results with
changing

and t1.CREATE_TIME < t2.CREATE_TIME

into this:

and t1.CREATE_TIME = (select MAX(t.CREATE_TIME) from @T t where
t.ITEM_ID=t2.ITEM_ID and t.CREATE_TIME < t2.CREATE_TIME)

Your solutions have less joining in them and look more streamlined.
All I need now is to teach myself this new syntax.
Thanks again.