Count of Request inflow per hour
This report provides a quick view of monthly opened incidents per hour. It provides you a quick determination of which hour have a large number of incidents opened. This information can then be used by an Incident Management administrator to determine if a particular department requires additional resources to efficiently manage the number of incidents.
To make any changes to a query, refer to the KB article below.
PGSQL
SELECT EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) "created time", CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=1 THEN '1. JAN' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =2 THEN '2. FEB' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =3 THEN '3. MAR' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =4 THEN '4. APR' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =5 THEN '5. MAY' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =6 THEN '6. JUN' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =7 THEN '7. JUL' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =8 THEN '8. AUG' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =9 THEN '9. SEP' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))= 10 THEN '10. OCT' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))= 11 THEN '11. NOV' WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))= 12 THEN '12. DEC' ELSE NULL END "MONTH", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =0 THEN 1 ELSE NULL END) "0-1", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =1 THEN 1 ELSE NULL END) "1-2", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =2 THEN 1 ELSE NULL END) "2-3", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=3 THEN 1 ELSE NULL END) "3-4", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =4 THEN 1 ELSE NULL END) "4-5", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =5 THEN 1 ELSE NULL END) "5-6", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =6 THEN 1 ELSE NULL END) "6-7", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =7 THEN 1 ELSE NULL END) "7-8", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =8 THEN 1 ELSE NULL END) "8-9", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =9 THEN 1 ELSE NULL END) "9-10", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =10 THEN 1 ELSE NULL END) "10-11", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =11 THEN 1 ELSE NULL END) "11-12", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =12 THEN 1 ELSE NULL END) "12-13", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =13 THEN 1 ELSE NULL END) "13-14", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =14 THEN 1 ELSE NULL END) "14-15", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =15 THEN 1 ELSE NULL END) "15-16", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =16 THEN 1 ELSE NULL END) "16-17", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =17 THEN 1 ELSE NULL END) "17-18", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =18 THEN 1 ELSE NULL END) "18-19", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =19 THEN 1 ELSE NULL END) "19-20", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =20 THEN 1 ELSE NULL END) "20-21", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =21 THEN 1 ELSE NULL END) "21-22", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =22 THEN 1 ELSE NULL END) "22-23", COUNT(CASE WHEN EXTRACT (HOUR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) =23 THEN 1 ELSE NULL END) "23-0" from workorder wo GROUP BY EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) , EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) ORDER BY 1,2
MSSQL
SELECT YEAR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) 'created time', CASE WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=1 THEN '1. JAN' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=2 THEN '2. FEB' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=3 THEN '3. MAR' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=4 THEN '4. APR' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=5 THEN '5. MAY' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=6 THEN '6. JUN' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=7 THEN '7. JUL' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=8 THEN '8. AUG' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=9 THEN '9. SEP' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=10 THEN '10. OCT' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=11 THEN '11. NOV' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00'))=12 THEN '12. DEC' ELSE NULL END 'MONTH', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =0 THEN 1 ELSE NULL END) '0-1', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =1 THEN 1 ELSE NULL END) '1-2', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =2 THEN 1 ELSE NULL END) '2-3', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =3 THEN 1 ELSE NULL END) '3-4', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =4 THEN 1 ELSE NULL END) '4-5', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =5 THEN 1 ELSE NULL END) '5-6', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =6 THEN 1 ELSE NULL END) '6-7', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =7 THEN 1 ELSE NULL END) '7-8', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =8 THEN 1 ELSE NULL END) '8-9', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =9 THEN 1 ELSE NULL END) '9-10', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =10 THEN 1 ELSE NULL END) '10-11', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =11 THEN 1 ELSE NULL END) '11-12', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =12 THEN 1 ELSE NULL END) '12-13', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =13 THEN 1 ELSE NULL END) '13-14', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =14 THEN 1 ELSE NULL END) '14-15', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =15 THEN 1 ELSE NULL END) '15-16', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =16 THEN 1 ELSE NULL END) '16-17', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =17 THEN 1 ELSE NULL END) '17-18', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =18 THEN 1 ELSE NULL END) '18-19', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =19 THEN 1 ELSE NULL END) '19-20', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =20 THEN 1 ELSE NULL END) '20-21', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =21 THEN 1 ELSE NULL END) '21-22', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =22 THEN 1 ELSE NULL END) '22-23', COUNT(CASE WHEN HOUR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) =23 THEN 1 ELSE NULL END) '23-0' FROM workorder GROUP BY MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')), YEAR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (CREATEDTIME/1000), '1970-01-01 00:00:00')) ORDER BY 1, 2
Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report.
New to ADSelfService Plus?
Related Articles
Query to show number of tickets created per template with their status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT reqtl.TEMPLATENAME "Request Template", count(wo.WORKORDERID) "Request Count", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Completed", count(case when std.ispending='1' THEN 1 ELSE ...Update Requester from Request Description
Use case: When a request is submitted on behalf of a user via email, the request description contains the requester's full name. Script to update the ticket requester accordingly. Tested on builds: 14306,14500. Steps to follow: Go to Admin > Request ...Query to show tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are ...Query to show count of tickets in each module per technician (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute this query. SELECT 'Request' "Module",aaau.first_name "Technician",count(wo.WORKORDERID) "Request count" FROM WorkOrder wo left join workorderstates wos ON ...Increase/decrease Log Count
It is possible to increase/decrease the log files count, however increasing the files will have an impact on the performance of the application. Also it is not recommended to increase the count more than 30. The serverout count can be decreased in ...