self join problem

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.

On Jun 8, 2:31 pm, Erland Sommarskog wrote:
> migurus ( ) writes:
> > 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
>
> 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, esq... DeleteThis @sommarskog.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

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.