cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
308
Views
10
Helpful
2
Replies

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

avalheru
Beginner
Beginner

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

1 Accepted Solution

Accepted Solutions

TalkingScientist
Beginner
Beginner

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

View solution in original post

2 Replies 2

TalkingScientist
Beginner
Beginner

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

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

Getting Started

Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community:

Recognize Your Peers