Count within a time period

The query you require is a very simple SQL statement using the GROUP BY clause. Because you are asking information that needs grouping by date only and also information that needs grouping by date and by technician you should use GROUPING SETS.

select year(closed) as year
, month(closed) as month
, Technician
, count(*) as count
, avg(datediff(DAY,Opened, Closed)) as avgDays
from jobs
where not Closed is null

group by grouping sets
    (year(closed), month(Closed)),
    (year(closed), month(Closed), technician)
order by year(closed)
, month(closed)
, Technician

If you want the jobs that are not closed remove the NOT in the WHERE clause.

Browse More Popular Posts

Leave a Comment