11-16-2022 07:18 AM
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
Solved! Go to Solution.
11-16-2022 08:00 AM
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
11-16-2022 08:00 AM
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
11-16-2022 01:50 PM
Thank you. I'll pass that along and hope it helps
Discover and save your favorite ideas. Come back to expert answers, step-by-step guides, recent topics, and more.
New here? Get started with these tips. How to use Community New member guide