CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-by
tripper
tripper

Reputation: 27

SQL query to include time segments with no counts

I am working in SQL Server 2014. I have table that records 'counts' and a timestamp of the count. The counting period is a two hour block that can start at any quarter hour. In the example data below, the count starts at 16:00 and goes through 18:00. The counting block could have started at 01:30 and stopped at 03:30.

Timestamp   Count
16:00:31    1
16:00:42    1
16:16:04    1
16:16:06    1
16:45:10    1
16:45:31    1
16:45:32    1
17:16:45    1
17:16:52    1
17:16:53    1
17:33:19    1
17:34:01    1
17:45:03    1
17:46:08    1

I have a query which sums the counts over 15 minute intervals within the two hour block:

SELECT
    FORMAT(DATEPART(HOUR, [Timestamp]), '0#') + ':' + FORMAT(DATEPART(MINUTE, [TimeStamp]) / 15 * 15, '0#') AS QtrHrBeg
    , COUNT(*) AS CountTotal
FROM 
    [Sandbox].[trippetoe].[SURVEYCOUNTS]
GROUP BY 
    DATEPART(HOUR, [TIMESTAMP])
    , (DATEPART(MINUTE, [TIMESTAMP]) / 15 * 15)

which results in this:

QtrHrBeg    Count
16:00   2
16:15   2
16:45   3
17:15   3
17:30   2
17:45   2

I'd like to include 15 minute intervals where there are no counts - in this example the quarter hours beginning at 16:30 and 17:00, like below: 

QtrHrBeg    Count
16:00   2
16:15   2
16:30   0
16:45   3
17:00   0
17:15   3
17:30   2
17:45   2

How can i do that?

Upvotes: 1

Views: 550

Answers (2)

Monah
Monah

Reputation: 6784

You can use the following

  1. Find the minimum date and the maximum date in the data you are going to work on , then round these two values to the nearest 15
  2. Split the segment into 15 minutes intervals
  3. Left join your data with the result came out and apply group by the StartTime and I used format in order to show the time formatting only

The benefit of this approach is that it works on specific interval and will not take any time interval outside of your data ranges.

with initial as(
  select dateadd(minute, datediff(minute,0,min([Time])) / 15 * 15, 0) as MinTime,
         dateadd(minute, datediff(minute,0,max([Time])) / 15 * 15, 0) as MaxTime
  from data
 ), times as(
   select StartTime = MinTime,
          EndTime =dateadd(millisecond,-1,dateadd(minute,15,MinTime)),
          MaxTime
   from initial
   union all
   select dateadd(millisecond,1,EndTime),
          dateadd(minute,15,EndTime),
          MaxTime
   from times
   where EndTime<MaxTime
 )

 select format(t.StartTime,'HH:mm') as [Time],isnull(sum(d.[Count]),0) as [Count]
 from times t
 left join data d on d.[Time] between t.StartTime and t.EndTime
 group by t.StartTime

Here is the output

Time    Count
16:00   2
16:15   2
16:30   0
16:45   3
17:00   0
17:15   3
17:30   2
17:45   2

Here a working demo

Hope this will help you

EDIT

I changed the usage of second to millisecond based on the comment from @HABO, it will solve the case where there is some times like 16:59:59

Upvotes: 0

Dave C
Dave C

Reputation: 7392

See below.

Begin by creating a time table of all intervals for the day, then restricting that to the intervals for the 2 hour window you want.

Then left join that to the sum of your data table, pushing 0 where the join returns null.

DECLARE @Data TABLE ([TimeStamp] TIME, [Count] INT)
INSERT INTO @Data ([TimeStamp],[Count])
VALUES ('16:00:31',1),
       ('16:00:42',1),
       ('16:16:04',1),
       ('16:16:06',1),
       ('16:45:10',1),
       ('16:45:31',1),
       ('16:45:32',1),
       ('17:16:45',1),
       ('17:16:52',1),
       ('17:16:53',1),
       ('17:33:19',1),
       ('17:34:01',1),
       ('17:45:03',1),
       ('17:46:08',1)

;with AllIntervals AS
(
    SELECT CONVERT(TIME,'00:00:00') AS Interval
    UNION ALL
    SELECT DATEADD(MINUTE,15,Interval)
    FROM AllIntervals
    WHERE Interval<'23:45:00'
), MyIntervals AS
    (
        SELECT CONVERT(VARCHAR(5),Interval,108) AS Interval
        FROM AllIntervals
        WHERE Interval >= (SELECT MIN(CONVERT(TIME,DATEADD(minute,(DATEDIFF(minute,0,[TimeStamp])/15)*15,0))) FROM @Data)
          AND Interval < DATEADD(HOUR,2,(SELECT MIN(CONVERT(TIME,DATEADD(minute,(DATEDIFF(minute,0,[TimeStamp])/15)*15,0))) FROM @Data))
    )

SELECT M.Interval, ISNULL(I.[Count],0)
FROM MyIntervals M
LEFT JOIN (SELECT CONVERT(TIME,DATEADD(minute,(DATEDIFF(minute,0,[TimeStamp])/15)*15,0)) AS Interval, SUM([Count]) AS Count
           FROM @Data
           GROUP BY CONVERT(TIME,DATEADD(minute,(DATEDIFF(minute,0,[TimeStamp])/15)*15,0))) I
   ON M.Interval=I.Interval

Upvotes: 1

Related Questions