CODEWITHSUNDEEP
sqljoin
Phillip O.
Phillip O.

Reputation: 135

How do you stop SQL Joins from processing a row twice

Let me start by saying I know my subject is not exactly correct but I don't know how to phrase it.

Rather than try to explain what I am trying to do, let me show you:

Below is a snippet of the pertinent data from my table (I am using CONVERT to trim date/time fields to the values we need. All dates are "XX/XX/XXXX 00:00:00.000" and all times are "01/01/1900 XX:XX:XX.000" where XX is a true value and 00 is placeholder).

CALLNBR     DATE       START      END
----------- ---------- ---------- ----------
0000182867  03/07/2012 10:55:00   12:20:00
0000182867  03/07/2012 12:20:00   13:00:00
0000182779  03/06/2012 14:29:00   15:03:00
0000182749  03/06/2012 15:15:00   15:30:00
0000182748  03/07/2012 10:40:00   12:30:00
0000182748  03/07/2012 12:30:00   13:20:00
0000182740  03/06/2012 11:00:00   11:30:00
0000182740  03/06/2012 11:30:00   12:00:00
0000182735  03/07/2012 09:10:00   10:00:00
0000182735  03/07/2012 10:00:00   10:40:00
0000182735  03/06/2012 14:40:00   15:10:00
0000182735  03/06/2012 15:10:00   15:30:00
0000182735  03/06/2012 15:30:00   16:45:00

I need to string times together where one records end time is the next one's start time. I am trying to get a result similar to (Filtered by CALLNBR 182735):

CallNbr     DATE       t1Start    t1end      t2Start    t2end      t3Start    t3end
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000182735  03/06/2012 14:40:00   15:10:00   15:10:00   15:30:00   15:30:00   16:45:00
0000182735  03/07/2012 09:10:00   10:00:00   10:00:00   10:40:00   NULL       NULL

But the result I AM getting is:

CallNbr     DATE       t1Start    t1end      t2Start    t2end      t3Start    t3end
----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0000182735  03/06/2012 14:40:00   15:10:00   15:10:00   15:30:00   15:30:00   16:45:00
0000182735  03/06/2012 15:10:00   15:30:00   15:30:00   16:45:00   NULL       NULL
0000182735  03/07/2012 09:10:00   10:00:00   10:00:00   10:40:00   NULL       NULL

My issue is with the middle record, whose values are in the first record returned:

0000182735  03/06/2012 15:10:00   15:30:00   15:30:00   16:45:00   NULL       NULL

Unfortunately I can't suppress NULLS by t3Start or t3End because that would eliminate the record for (in this instance) 03/07/2012.

0000182735  03/07/2012 09:10:00   10:00:00   10:00:00   10:40:00   NULL       NULL

And now for the SELECT (To make it more readable, I am stripping out my CONVERT'ers):

    SELECT
      t1.CallNbr,
      t1.STRTDATE,
      t1.strttime as t1Start,
      t1.endtme   as t1end,
      t2.strttime as t2Start,
      t2.endtme   as t2end,
      t3.strttime as t3Start,
      t3.endtme as t3end
    FROM       TableA t1
    INNER JOIN TableA t2 ON t1.endtme    = t2.strttime
                        AND t1.strtdate  = t2.strtdate
                        AND t1.CALLNBR   = t2.CALLNBR
                        AND t1.LINITMTYP = 'L' 
    LEFT  JOIN TableA t3 ON t3.CALLNBR  = t2.CALLNBR
                        AND t3.strttime = t2.endtme
                        AND t3.strtdate = t1.strtdate
    WHERE t1.CALLNBR = '0000182735'
    ORDER BY t1.CALLNBR, t1.strtdate

Even if you can't help, thank you for taking the time to read through it.

Thanks,

Phillip

Upvotes: 6

Views: 201

Answers (1)

Justin Pihony
Justin Pihony

Reputation: 67115

If you are able to use the ROW_NUMBER() functionality, then I would do the following. This makes sure that the query only pulls from the starting, and not the middle. You can possibly clean the query up a little this way, but I am not sure. All you should need is the RowNum = 1, but you can add extra security of not allowing any overlap using parent table rownum > child table rownum that I added in comments

SELECT t1.CallNbr, t1.StrtDate, t1.StrtTime, t1.EndTime, t1.LINITMTYP,
    ROW_NUMBER() OVER (PARTITION BY t1.CALLNBR, t1.strtdate ORDER BY t1.StrtTime) AS RowNum
INTO #MyTemp
FROM TableA AS t1

SELECT
      t1.CallNbr,
      t1.STRTDATE,
      t1.strttime as t1Start,
      t1.endtme as t1end,
      t2.strttime as t2Start,
      t2.endtme as t2end,
      t3.strttime as t3Start,
      t3.endtme as t3end
    FROM #MyTemp t1
        JOIN #MyTemp t2 ON t1.endtme    = t2.strttime
                  AND t1.strtdate  = t2.strtdate
                  AND t1.CALLNBR   = t2.CALLNBR
                  AND t1.LINITMTYP = 'L' 
                  --You could add extra security using the following
                  --AND t2.RowNum > t1.RowNum
    LEFT JOIN #MyTemp t3 ON t3.CALLNBR  = t2.CALLNBR
                       AND t3.strttime = t2.endtme
                       AND t3.strtdate = t1.strtdate
                       --You could add extra security using the following
                       --AND t3.RowNum > t2.RowNum
    WHERE t1.CALLNBR = '0000182735' 
        AND t1.RowNum = 1
    ORDER BY t1.CALLNBR, t1.strtdate

Upvotes: 1

Related Questions