CODEWITHSUNDEEP
sqlsql-server
user3727436
user3727436

Reputation: 97

Displaying a count in SQL for timerange even if rows show 0

So I have a query to pull a count of errors from a log table for a time range and display the count per minute.

select DATEADD(MI, DATEDIFF(MI, 0, errors),0), COUNT(*) from log
where errors> '2014-07-23 17:20'
and errors < '2014-07-23 17:25'
group by DATEADD(MI, DATEDIFF(MI, 0, errors),0)

If there were no errors for a minute it would just omit that row:

2014-07-23 17:20:00.000 20
2014-07-23 17:21:00.000 20
2014-07-23 17:23:00.000 20
2014-07-23 17:24:00.000 19

How can i get it to populate a row even if there are no errors. E.g. the above output would have a row like:

2014-07-23 17:22:00.000 0

Upvotes: 0

Views: 57

Answers (1)

jpw
jpw

Reputation: 44891

Generate a range for the dates you need to fill:

-- test table, should be the results from your query
declare @t table (d datetime, c int)
insert @t values 
('2014-07-23 17:20:00.000', 20),
('2014-07-23 17:21:00.000', 20),
('2014-07-23 17:23:00.000', 20),
('2014-07-23 17:24:00.000', 19);

with cte (d) as (
    select cast('2014-07-23 17:20' as datetime) as d
    union all
    select DATEADD(minute,1,d) d
    from cte where d < cast('2014-07-23 17:25' as datetime)
)

select isnull(t.d, cte.d), isnull(c,0) 
from cte
left join @t t on cte.d = t.d

Output:

----------------------- -----------
2014-07-23 17:20:00.000 20
2014-07-23 17:21:00.000 20
2014-07-23 17:22:00.000 0
2014-07-23 17:23:00.000 20
2014-07-23 17:24:00.000 19
2014-07-23 17:25:00.000 0

In your case the query would probably be something like:

with cte (d) as (
    select cast('2014-07-23 17:20' as datetime) as d
    union all
    select DATEADD(minute,1,d) d
    from cte where d < cast('2014-07-23 17:25' as datetime)
)
select isnull(t.d, cte.d), isnull(c,0) 
from cte
left join(
    select DATEADD(MI, DATEDIFF(MI, 0, errors),0) as d, COUNT(*) from log
    where errors> '2014-07-23 17:20'
    and errors < '2014-07-23 17:25'
    group by DATEADD(MI, DATEDIFF(MI, 0, errors),0)
    ) derived on cte.d = derived.d

Upvotes: 1

Related Questions