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

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

Re: self join problem

Show all headers | View raw

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

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