Call Type Queue Interval - does not show intervals with 0 calls

[avalheru]

I'm trying to build out this report but it only shows intervals that had calls.  If there are no calls during that interval, the report skips the interval.  Is there a way I can show all intervals during a time period?
For example, I run the report from 5AM to 6PM.  I want to see
5:00
5:15
5:30
and all the way up to 6PM, even if some intervals are all zeros due to no calls coming in.

Reason is I'm building out daily reporting that I will use to roll up weekly and monthly and my rows will never line up if different intervals are skipped on a daily basis.  I want a report where the same intervals will be in the same excel cell every time I run and export

[TalkingScientist · Accepted solution]

This was a fun challenge.  I was able to put something together that works for individual days. Shouldn't be to hard to tweak it for more but ran into recursion limits when generating the interval list and I didn't have much free time.

DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
SET @StartDate = '2022-11-16';


WITH IntervalTable AS 
(
    SELECT CAST(@StartDate AS DATETIME) + CAST('00:00:00.000' AS DATETIME) AS DateTime
    UNION ALL
    SELECT DATEADD(MINUTE,15,DateTime) FROM IntervalTable
    WHERE DateTime< CAST(@StartDate AS DATETIME) + CAST('23:45:00.000' AS DATETIME)
)
SELECT
	IntervalTable.DateTime,
	ct.*,
	ctsg.* 
FROM IntervalTable
	CROSS JOIN Call_Type ct
	LEFT JOIN Call_Type_SG_Interval ctsg ON IntervalTable.DateTime = ctsg.DateTime AND ct.CallTypeID = ctsg.CallTypeID
WHERE
	ctsg.DateTime >=CAST(@StartDate AS DATETIME) + CAST('00:00:00.000' AS DATETIME) AND ctsg.DateTime < CAST(@StartDate AS DATETIME) + CAST('23:45:00.000' AS DATETIME)

ORDER BY IntervalTable.DateTime, ctsg.CallTypeID

[avalheru]

Thank you.  I'll pass that along and hope it helps