Count of Request inflow per hour

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.


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. 

          • 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 > ...
          • 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 ...
          • How to update a request based on certain field values.

            This post describes the use of a python script to update requests based on values in other fields ,in the same request.This action can be performed using  Custom Triggers. UseCase: There are three Levels setup in the application and there are 2 ...