Times by quarterhour
-
I have build a reference table called QtrHour. Values
00 00-14 N
00 15-29 N
00 30-44 N
00 45-59 N
01 00-14 N
01 15-29 N
01 30-44 N
01 45-59 N
02 00-14 N
02 15-29 N
02 30-44 N
02 45-59 N etc.
I want to use this in a join query to return all calls for each time period. If there were no calls, I wish to return zeros or no data. This query returns the data perfectly but only if there were calls. I think I can join differently to pull all the values from QtrHour.
My query is:
SELECT QtrHour.[Hour], QtrHour.QtrHour, QtrHour.Shift, COALESCE (SUM(Call_QtrHour_Disposition.TotalHoldSecs), 0) AS TotalHoldSecs
FROM QtrHour LEFT OUTER JOIN
Call_QtrHour_Disposition ON QtrHour.[Hour] = Call_QtrHour_Disposition.[Hour] AND QtrHour.QtrHour = Call_QtrHour_Disposition.QtrHour
WHERE (Call_QtrHour_Disposition.Entered_Queue > '2/1/2006') AND (Call_QtrHour_Disposition.Entered_Queue < '2/2/2006')
GROUP BY QtrHour.[Hour], QtrHour.QtrHour, QtrHour.Shift
ORDER BY QtrHour.[Hour], QtrHour.QtrHour
Results:
01 30-44 N 4
01 45-59 N 283
02 00-14 N 16
02 30-44 N 2
Missing values for
00 00-14 N
00 15-29 N
00 30-44 N
00 45-59 N
01 00-14 N
01 15-29 N
-
not tested but ...Something like this...
declare @results table
(
[Hour] int,
QTR_Hour datetime,
Shift char(1) default 'N',
TotalHoldSecs int default 0
)
--set a @start and @end time manually if you want
declare @start datetime,
@end datetime
set @start = min(QTR_Hour)
from QtrHour
set @end = max(@QTR_HOur)
from QtrHour
while @start @start) AND (Call_QtrHour_Disposition.Entered_Queue < @end)
GROUP BY QtrHour.[Hour], QtrHour.QtrHour, QtrHour.Shift
ORDER BY QtrHour.[Hour], QtrHour.QtrHour
)q
on r.QtrHour = q.QTR_Hour
select * from @results
HTH
Mathew J Kulangara
sqladventures.blogspot.com -
The only issue here is the classic mistake of LEFT JOIN, but then placing a WHERE condition on the joined table, thereby implicitly forcing it back to an INNER JOIN:
FROM QtrHour LEFT OUTER JOIN
Call_QtrHour_Disposition ON QtrHour.[Hour] = Call_QtrHour_Disposition.[Hour] AND QtrHour.QtrHour = Call_QtrHour_Disposition.QtrHour
WHERE (Call_QtrHour_Disposition.Entered_Queue > '2/1/2006') AND (Call_QtrHour_Disposition.Entered_Queue < '2/2/2006')
Re-write as:
FROM QtrHour
LEFT OUTER JOIN Call_QtrHour_Disposition
ON QtrHour.[Hour] = Call_QtrHour_Disposition.[Hour] AND
QtrHour.QtrHour = Call_QtrHour_Disposition.QtrHour AND
Call_QtrHour_Disposition.Entered_Queue > '2/1/2006' AND
Call_QtrHour_Disposition.Entered_Queue < '2/2/2006'
-
Thanks PW. I new there was a way and I just kept fiddling with views etc with no success. I do appreciate your answer!! I have read over and over about not forcing SQL re: execution plan, indexes etc, but continue to be occluded by the trees when wandering in the forest!!!
Steve
-
Nice to learn that I'm not the only one who's made Classic mistakes...
PW - I'm curious why you included the test in the JOIN instead of modifying the WHERE clause as I might have. Does one method perform better than the other?
Mike
WHERE ( (Call_QtrHour_Disposition.Entered_Queue > '2/1/2006') AND (Call_QtrHour_Disposition.Entered_Queue < '2/2/2006') ) OR Call_QtrHour_Disposition.ID IS NULL
-
>>I'm curious why you included the test in the JOIN
2 reasons
- Code readability/maintainability
- Performance & index usage
Maintenance: Consider if there was more than 1 column being filtered on, in the left-joined table. You'd need to add the OR ... IS NULL for each column. More AND/OR logical conditions that need to be bracketed correctly. 1 misplaced bracket and you've accidentally changed the logic.
Performance: If the column being filtered on participates in a usable index, the index will be used in the JOIN form and not used in the WHERE ... OR ... IS NULL form
Best to show an example. Simple master-detail temp tables. Query needs to filter on a column in the detail table, which is being left-joined to. 1st query is using a LEFT JOIN, 2nd filters in the WHERE. Run this in QA and check the query execution plans.
Create Table #Master
(
RowID int,
SomeData varchar(50)
)
Create Table #Detail
(
MasterRowID int,
DetailRowID int,
Status int,
SomeMoreData varchar(50)
)
Create index #ixDetailStatus on #Detail (Status, MasterRowID, DetailRowID)
Insert Into #Master
Select 1, 'Master Row 1' Union
Select 2, 'Master Row 2' Union
Select 3, 'Master Row 3'
Insert Into #Detail
Select 1, 1, 1, 'Detail 1,1' Union
Select 1, 2, 0, 'Detail 1,2' Union
Select 3, 1, 1, 'Detail 3,1' Union
Select 3, 1, 0, 'Detail 3,1'
-- LEFT JOIN, with filter in the JOIN
Select *
From #Master As m
Left Outer Join #Detail As d
On (m.RowID = d.MasterRowID And d.Status = 1)
-- LEFT JOIN, with filter in the WHERE, with a NULL check
Select *
From #Master As m
Left Outer Join #Detail As d
On (m.RowID = d.MasterRowID)
Where (d.Status = 1 Or d.Status Is Null)
Query plan for the JOIN
|--Bookmark Lookup(BOOKMARK
[Bmk1001]), OBJECT[tempdb].[dbo].[#Detail__________________________________________________________ AS [d]))|--Nested Loops(Left Outer Join, OUTER REFERENCES
[m].[RowID]))|--Table Scan(OBJECT
[tempdb].[dbo].[#Master__________________________________________________________ AS [m]))|--Index Seek(OBJECT
[tempdb].[dbo].[#Detail________________.[#ixDetailStatus] AS [d]),SEEK
[d].[Status]=1 AND [d].[MasterRowID]=[m].[RQuery plan for the WHERE
|--Filter(WHERE
[d].[Status]=1 OR [d].[Status]=NULL))|--Nested Loops(Left Outer Join, WHERE
[m].[RowID]=[d].[MasterRowID]))|--Table Scan(OBJECT
[tempdb].[dbo].[#Master___________________________________________________________AS [m]))|--Table Scan(OBJECT
[tempdb].[dbo].[#Detail____________________________________________________________AS [d])) -
PK
I exercised your example, and although I had to force the JOIN version to use the index with a WITH ( INDEX ... ) it clearly is more efficient than my WHERE version.
Thanks
Mike
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply