Count within a time period

by

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.

More Related Contents:

  1. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  2. How can I escape square brackets in a LIKE clause?
  3. Parameter Sniffing (or Spoofing) in SQL Server
  4. How can I truncate a datetime in SQL Server?
  5. What are the main performance differences between varchar and nvarchar SQL Server data types?
  6. SQL Server String or binary data would be truncated
  7. Datatype for storing ip address in SQL Server
  8. SQL Server: Query fast, but slow from procedure
  9. SQL Server Regular expressions in T-SQL
  10. Concatenating Column Values into a Comma-Separated List
  11. List the queries running on SQL Server
  12. SQL Server pivot vs. multiple join
  13. SQL Server Bulk insert of CSV file with inconsistent quotes
  14. What is a free tool to compare two SQL Server Databases? [closed]
  15. SQL Server Query: Fast with Literal but Slow with Variable
  16. How can I make HTTP request from SQL server?
  17. SQL Server Management Studio alternatives to browse/edit tables and run queries [closed]
  18. Format SQL in SQL Server Management Studio
  19. SQL Server, the misleading XLOCK & optimizations
  20. Grant Select on a view not base table when base table is in a different database
  21. SQL Server (localdb)\v11.0 explained
  22. migrate data from MS SQL to PostgreSQL?
  23. SQL Server – join rows into comma separated list
  24. What are the differences between Merge Join and Lookup transformations in SSIS?
  25. Table Valued Function Killing My Query Performance
  26. freeTDS not using its config
  27. Pivot multiple columns based on one column in SQL Server
  28. @@ERROR and/or TRY – CATCH
  29. How to make a passthrough / passthru query editable?
  30. How to do Sql Server CE table update from another table

Leave a Comment