Group by datetime minutes - Show minutes with 0 entries in database?

Question

I'm trying to write a T-SQL query that looks for amount of entries in a table, over a certain period of time, and then group them by minute (entries/minute). But I cant get it to show the minutes with 0 entries.

My query looks like:

select
[Minute],
Files
from
(
    select 
        (DATEDIFF(MINUTE, '2017-02-02 17:00:00.000', RegTime) / 1) as [Minute] ,
        count(*) as Files
            from TransferLog
            WHERE RegTime BETWEEN '2017-02-02 17:00:00.000' AND '2017-02-03 04:00:00.000'
        group BY
        (DATEDIFF(MINUTE, '2017-02-02 17:00:00.000', RegTime) / 1) 
) x  order by [Minute]

Any Ideas?

Edit1. Excepted output would show minutes with no entries in the table. Also, the query is rather unusable when this occurs since I can't know which minute had no data. For example if I want data from 800 minutes but 20 of the minutes had no data written to the table this would only show 780 minutes worth of data. Is there anyway to sort the data or modify the query for this purpose?

Expected output:
Minute | Files
0   685
1   0
2   672
3   0
4   415
5   434
6   746

-

Current Output:
Minute | Files
0   685
1   672
2   415
3   434
4   746

Every row in your result is generated from one or more rows from your input tables. You don't get rows with zeroes because... there's no row to supply that datetime value. — Damien_The_Unbeliever, Feb 03 '17 at 10:20
You may need to join to a calendar table to bring in the minutes whose diffs do not appear in your data set. — Tim Biegeleisen, Feb 03 '17 at 10:21

score 0 · Accepted Answer · edited May 23 '17 at 12:01

You can create a temp table holding all the minutes you need from 1 to the maximum minute you have (for example using this tecnique), then you can left join your data table with the temp table replacing null values with zeros.

Thus you should have all the minutes listed with your values where you have data and zeros elsewhere.

Here is the code:

declare @max_minute      int
declare @dataTable       table ([Minute] int, Files int)

--insert your data into a temp table
insert into @dataTable
select  [Minute],   Files
from
(select 
        (DATEDIFF(MINUTE, '2017-02-02 17:00:00.000', RegTime) / 1) as [Minute] ,
        count(*) as Files
            from TransferLog
            WHERE RegTime BETWEEN '2017-02-02 17:00:00.000' AND '2017-02-03 04:00:00.000'
        group BY
        (DATEDIFF(MINUTE, '2017-02-02 17:00:00.000', RegTime) / 1) 
) x  order by [Minute]

--calculate maximum minute from your data
select @max_minute = max([Minute]) from @dataTable

;
WITH gen AS (
    SELECT 1 AS Minute
    UNION ALL
    SELECT Minute+1 FROM gen WHERE Minute+1<=@max_minute
)
SELECT  M.Minute, isnull(D.Files,0) as Files  
FROM gen  M left join @dataTable D on M.Minute = D.Minute
option (maxrecursion 10000)

Wow! I couldnt contain myself and had to try this right away and it worked like a charm. Thanks again! — P.Paulsson, Feb 03 '17 at 20:22

Group by datetime minutes - Show minutes with 0 entries in database?

1 Answers1