Grouping incident counts into 5 minute time segmen

问题:

Hoping someone can assist with how to modify the following SQL to achieve the result shown in desired output. I am not fluent in TSQL but know enough to get this far.
My objectrive is to count my incident data and group into 15 minutes time segments starting at midnight but also include zero (0) where there is no incident data in a particular time segment.

Curent Query#

;With cte As
(SELECT CONVERT (varchar(5),DATEADD(minute, 15 * 
   (DATEDIFF(minute, '20000101', I.CreateTimestamp) / 15), '20000101'),108) 
    AS CreationTime, I.IncidentShortReference AS Ref
FROM Incident I
WHERE i.CreateTimestamp between DATEADD(d,-1,GETDATE()) and  GETDATE()
)
SELECT CTE.CreationTime, count(CTE.Ref) As Count
FROM cte CTE
GROUP BY CTE.CreationTime
ORDER BY CTE.CreationTime

My result

CreationTime    count
00:15           2
01:00           1
01:15           1
01:30           1
01:45           2
02:00           1
02:15           1
02:30           4
(Truncated)

Desired Output

CreationTime    count
00:15           2
00:30           0
00:45           0
01:00           1
01:15           1
01:30           1
01:45           2
02:00           1
02:15           1
02:30           4
02:45           0
03:00           0
(Truncated)

回答1:

This uses a cte creating a record for every timestamp between midnight yesterday and now, with a count for the number of incidents in each range with sample data:

declare @incident table (CreateTimestamp datetime, IncidentShortReference  varchar(5))
insert into @incident values ('4/10/2017 11:11:00', 'test') 
insert into @incident values ('4/10/2017 11:12:00', 'test')
insert into @incident values ('4/10/2017 11:21:00', 'test')
insert into @incident values ('4/10/2017 11:31:00', 'test')
insert into @incident values ('4/10/2017 13:31:00', 'test')

DECLARE @dt datetime
SELECT @dt = dateadd(d, datediff(d, 0, getdate()), 0) - 1 -- yesterday at midnight

;with cte as
(
select @dt dt
union all
select DATEADD(minute, 15, dt) as Next15
FROM cte
WHERE DATEADD(minute, 15, dt) < GETDATE()
)
select convert(varchar(5), dt, 108) as CreationTime, (select count(*) FROM @incident WHERE CreateTimestamp >= dt and CreateTimestamp < dateadd(mi, 15, dt)) as count
from cte

Sample output from a random interval:

回答2:

You could create a time interval CTE table like this

WITH TIME_CTE
AS(
    SELECT 
        CAST('20170411 00:15:00' AS DATETIME) AS TimePeriod
    UNION ALL
    SELECT 
        DATEADD(MINUTE, 15, TimePeriod)
    FROM TIME_CTE
    WHERE
        DATEADD(MINUTE, 15, TimePeriod) < CAST('20170411 23:59:00' AS DATETIME)
)

SELECT 
    LEFT(CONVERT(VARCHAR(10),TimePeriod,108), 5) 
FROM TIME_CTE

Then join it with your original query

WITH TIME_CTE
AS(
    SELECT 
        CAST('20170411 00:15:00' AS DATETIME) AS TimePeriod
    UNION ALL
    SELECT 
        DATEADD(MINUTE, 15, TimePeriod)
    FROM TIME_CTE
    WHERE
        DATEADD(MINUTE, 15, TimePeriod) < CAST('20170411 23:59:00' AS DATETIME)
), 
CTE 
AS (
SELECT CONVERT (varchar(5),DATEADD(minute, 15 * 
   (DATEDIFF(minute, '20000101', I.CreateTimestamp) / 15), '20000101'),108) 
    AS CreationTime, I.IncidentShortReference AS Ref
FROM Incident I
WHERE i.CreateTimestamp between DATEADD(d,-1,GETDATE()) and  GETDATE()
)

SELECT TIME_CTE.TimePeriod, SUM(IIF(CTE.Ref IS NULL, 0, 1)) As Count
FROM TIME_CTE 
     LEFT JOIN CTE ON CTE.CreationTime = TIME_CTE.TimePeriod
GROUP BY TIME_CTE.TimePeriod
ORDER BY TIME_CTE.TimePeriod