Creating missing minute records in time data to complete a day (1440 records per day)

Question

I have a data source which for all intents and purposes is based on web analytics, the table cardinality is 1 row = each minute of each hour of each date in a select date range. Sometimes this data does not return a record for every minute of the day (where no activity was registered in the data source during that minute). An example is as follows, with the missing minutes being 17:26 and 17:28:

+------------+------+--------+--------+---------+
|    Date    | Hour | Minute | Visits | Bounces |
+------------+------+--------+--------+---------+
| 2015-09-07 |   17 |     24 |    499 |      58 |
| 2015-09-07 |   17 |     25 |     32 |       4 |
| 2015-09-07 |   17 |     27 |     12 |       1 |
| 2015-09-07 |   17 |     29 |      6 |       0 |
+------------+------+--------+--------+---------+

For our purposes, we need each day to equate to it's full range of minutes, and these records where no activity was registered should not be absent from the database, but rather be entered as records with the measure columns null, which would output the following:

+------------+------+--------+--------+---------+
|    Date    | Hour | Minute | Visits | Bounces |
+------------+------+--------+--------+---------+
| 2015-09-07 |   17 |     24 |    499 |      58 |
| 2015-09-07 |   17 |     25 |     32 |       4 |
| 2015-09-07 |   17 |     26 |      0 |       0 |
| 2015-09-07 |   17 |     27 |     12 |       1 |
| 2015-09-07 |   17 |     28 |      0 |       0 |
| 2015-09-07 |   17 |     29 |      6 |       0 |
+------------+------+--------+--------+---------+

I've thought about utilizing the set functions to include minutes where they do not exist, but this would return the missing record as literally just the minute, no date or hour data would be carried into the record so I've got to admit I'm a little stumped!

score 2 · Accepted Answer · edited May 23 '17 at 10:26

You probably will need a minutes tables. There are procedures to automatic create a ranges of minutes. Look HERE for a sample with days.

CREATE TABLE minute
    ([Date] datetime, [Hour] int, [Minute] int)
GO

INSERT INTO minute
    ([Date], [Hour], [Minute])
VALUES
    ('2015-09-07 00:00:00', 17, 24),
    ('2015-09-07 00:00:00', 17, 25),
    ('2015-09-07 00:00:00', 17, 26),
    ('2015-09-07 00:00:00', 17, 27),
    ('2015-09-07 00:00:00', 17, 29)

SQL FIDDLE DEMO

 SELECT m.[Date], m.[Hour], m.[Minute], 
     CASE WHEN a.[Visits] is null then 0
          ELSE a.[Visits]
     END [Visits], 
     CASE WHEN a.[Bounces] is null then 0
          ELSE a.[Bounces]
     END [Bounces]
 FROM minute m
 left join analytic a
    on m.date = a.date
    and m.hour = a.hour
    and m.minute = a.minute

Ah of course! I had considered using standard joins but somehow ignored the fact that I could just do this with date as a comp key included! A bit of fresh thinking helps immensely, thanks Juan. — Ciaren, Sep 08 '15 at 09:44

score 1 · Answer 2 · answered Sep 07 '15 at 20:55

Although there may be better and simpler options, I would do this:

Create a table with all the values for hour and minute (1440 rows)
Insert the missing values

Assuming you have a table called tbl_hour_minute with the following structure:

HOUR  |  MINUTE
------+--------
 0    |   0
 0    |   1
 ...
 0    |   59
 1    |   0
 ...
 23   |   59

You could do something like this to get the missing minutes:

select c.*
from 
(
select a.date, b.hour, b.minute
from (select distinct date from your_table) as a
     tbl_hour_minute as b
) as c
left join your_table as t on c.date = t.date and c.hour = t.hour and c.minute = t.minute
where t.hour is null and t.minute is null;

You could then insert the data into your table, adding the needed zeros if needed.

Creating missing minute records in time data to complete a day (1440 records per day)

2 Answers2