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

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

Re: self join problem

Show all headers | View raw

migurus (migurus@yahoo.com) 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, esquel@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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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