Menu
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
830
Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.
Using an Incomplete ON Condition Unwanted rows in the result set may come from incomplete ON conditions. In some cases, you need to join tables by multiple columns. In these situations, if you use only one pair of columns, it results in duplicate rows.
SQL Delete Duplicate Rows using Group By and Having Clause According to Delete Duplicate Rows in SQL, for finding duplicate rows, you need to use the SQL GROUP BY clause. The COUNT function can be used to verify the occurrence of a row using the Group by clause, which groups data according to the given columns.
You are getting duplicates because more than one row matches your conditions. To prevent duplicates use the DISTINCT keyword: SELECT DISTINCT respid, cq4_1, dma etc...
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
