Request Management - number of requests submitted per hour

Count of request inflow per hour - provides an insight on monthly opened incidents per hour. It provides you a quick determination on timeframe that received a large number of incidents. 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.

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

Click this link to navigate to the next report.