Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #410

self join problem

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

self join problem migurus <migurus@yahoo.com> - 2011-06-07 19:24 -0700
  Re: self join problem Erland Sommarskog <esquel@sommarskog.se> - 2011-06-08 23:31 +0200
    Re: self join problem migurus <migurus@yahoo.com> - 2011-06-08 17:33 -0700

csiph-web