How to stop SQL connections from processing a string twice

Question

Let me start by saying that I know that my topic is not entirely correct, but I do not know how to express it.

Instead of trying to explain what I'm trying to do, let me show you:

Below is a snippet of the relevant data from my table (I use CONVERT to round the date and time fields to the required values. All dates: "XX / XX / XXXX 00: 00: 00.000" and all the time "01/01/1900 XX: XX: XX. 000 ", where XX is the true value and 00 is the 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 relate the time when one recording end time is the next one 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 get:

 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 problem is related to the average entry, the values ​​of which are indicated in the first entry:

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

Unfortunately, I cannot suppress NULLS with t3Start or t3End because it will eliminate the entry for (in this case) 07/03/2012.

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

And now for SELECT (to make it more readable, I delete 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 cannot help, thanks for taking the time to read it.

Thanks,

Phillip

Answer 1

If you can use the ROW_NUMBER () functionality, I would do the following. This ensures that the request only stretches from the start, and not from the average. You can clean up the request a bit this way, but I'm not sure. All you need is RowNum = 1, but you can add extra protection that does not allow overlapping using the parent table rownum> child table rownum, which I added in the 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