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
Update Task description with Request description.
Requirement: Copy the request description content to all the associated task description. Usecase: When a task is triggered from a request, it should contain the request description in the description of the task. This will enable task technician to ...Set Task Owner depending on the request field values
Requirement: Set Task Owner depending on the request field values. Usecase: When a task is triggered from a request, it should be assigned to a technician depending on the request field values. Steps to follow: Goto Admin > Task Custom Functions > ...Set Task Owner depending on the request site
Requirement: Set Task Owner depending on the request site. Usecase: When a task is triggered from a request, it should be assigned to a technician depending on the request site. Steps to follow: Goto Admin > Task Custom Functions > Global function > ...Change template count
SELECT ct.NAME "Template", Count(chdt.changeid) "count" FROM changedetails chdt LEFT JOIN changetemplate ct ON chdt.templateid=ct.templateid WHERE chdt.createdtime >= <from_lastmonth> ...Automatically close request after successive approval reminders.
This post describes the use of a python script to close requests after successive approval reminders using Custom Schedules. Use Case: We have an option under the self-service portal in order to send reminders mail for the approvals, what would be ...