Request Summary (Inbound,completed and overdue)

Request Summary (Inbound,completed and overdue)

This report provides a quick overview of monthly opened, closed and overdue incidents. It provides you a quick determination of 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. 

MSSQL

SELECT 'Inbound' "Status",  
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=1 THEN 1 ELSE NULL END) 'JAN',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END) 'FEB',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END) 'MAR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END) 'APR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END)  'MAY' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END)  'JUN' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END)  'JUL' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END)  'AUG' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END)  'SEP' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END)  'OCT' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END)  'NOV' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (CREATEDTIME/1000),'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END)  'DEC' from workorder where workorder.ISPARENT=1 AND wo.CREATEDTIME >= <from_thisyear> AND wo.CREATEDTIME <= <to_thisyear>
UNION
                                                                                                      

SELECT 'Completed' "Status", 
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=1 THEN 1 ELSE NULL END) 'JAN',
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END) 'FEB',
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END) 'MAR',
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END) 'APR',
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END)  'MAY' ,
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END)  'JUN' ,
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END)  'JUL' ,
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END)  'AUG' ,
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END)  'SEP' ,
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END)  'OCT' ,
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END)  'NOV' ,
COUNT(CASE WHEN COMPLETEDTIME=0 THEN NULL WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (COMPLETEDTIME/1000),'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END)  'DEC' from workorder where workorder.ISPARENT=1 AND completedtime >= <from_thisyear> AND completedtime <= <to_thisyear>       
                                                                                                                                                  

UNION
                                                                                                      

SELECT 'OverDue' "Status", 
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=1 THEN 1 ELSE NULL END) 'JAN',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=2 THEN 1 ELSE NULL END) 'FEB',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=3 THEN 1 ELSE NULL END) 'MAR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=4 THEN 1 ELSE NULL END) 'APR',
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=5 THEN 1 ELSE NULL END)  'MAY' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=6 THEN 1 ELSE NULL END)  'JUN' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=7 THEN 1 ELSE NULL END)  'JUL' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=8 THEN 1 ELSE NULL END)  'AUG' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=9 THEN 1 ELSE NULL END)  'SEP' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=10 THEN 1 ELSE NULL END)  'OCT' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=11 THEN 1 ELSE NULL END)  'NOV' ,
COUNT(CASE WHEN MONTH(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (DUEBYTIME/1000),'1970-01-01 00:00:00'))=12 THEN 1 ELSE NULL END)  'DEC' from workorder  LEFT JOIN WorkOrderStates ON workorder.WORKORDERID = WorkOrderStates.WORKORDERID where WorkOrderStates.ISOVERDUE=1 and workorder.ISPARENT=1 AND duebytime >= <from_thisyear> AND duebytime <= <to_thisyear>


PGSQL

SELECT EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) "Year", 'Inbound' "Status",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=1 THEN 1 ELSE NULL END) "JAN",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=2 THEN 1 ELSE NULL END) "Feb",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=3 THEN 1 ELSE NULL END) "Mar",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=4 THEN 1 ELSE NULL END)"Apr",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=5 THEN 1 ELSE NULL END)"May",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=6 THEN 1 ELSE NULL END)"June",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=7 THEN 1 ELSE NULL END)"July",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=8 THEN 1 ELSE NULL END)"Aug",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=9 THEN 1 ELSE NULL END)"Sep",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=10 THEN 1 ELSE NULL END)"Oct",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=11 THEN 1 ELSE NULL END)"Nov",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP))=12 THEN 1 ELSE NULL END)"Dec" from workorder wo group by EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) 
union
SELECT EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) "Year", 'Closed' "Status",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=1 THEN 1 ELSE NULL END) "JAN",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=2 THEN 1 ELSE NULL END) "Feb",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=3 THEN 1 ELSE NULL END) "Mar",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=4 THEN 1 ELSE NULL END)"Apr",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=5 THEN 1 ELSE NULL END)"May",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=6 THEN 1 ELSE NULL END)"June",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=7 THEN 1 ELSE NULL END)"July",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=8 THEN 1 ELSE NULL END)"Aug",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=9 THEN 1 ELSE NULL END)"Sep",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=10 THEN 1 ELSE NULL END)"Oct",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=11 THEN 1 ELSE NULL END)"Nov",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.COMPLETEDTIME/1000)::TIMESTAMP))=12 THEN 1 ELSE NULL END)"Dec" from workorder wo group by EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) 
Union
SELECT EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) "Year", 'Overdue' "Status",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=1 THEN 1 ELSE NULL END) "JAN",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=2 THEN 1 ELSE NULL END) "Feb",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=3 THEN 1 ELSE NULL END) "Mar",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=4 THEN 1 ELSE NULL END)"Apr",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=5 THEN 1 ELSE NULL END)"May",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=6 THEN 1 ELSE NULL END)"June",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=7 THEN 1 ELSE NULL END)"July",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=8 THEN 1 ELSE NULL END)"Aug",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=9 THEN 1 ELSE NULL END)"Sep",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=10 THEN 1 ELSE NULL END)"Oct",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=11 THEN 1 ELSE NULL END)"Nov",
Count(CASE WHEN EXTRACT(MONTH FROM (to_timestamp(wo.DUEBYTIME/1000)::TIMESTAMP))=12 THEN 1 ELSE NULL END)"Dec" from workorder wo group by EXTRACT(YEAR FROM (to_timestamp(wo.createdtime/1000)::TIMESTAMP)) 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 M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Query to show overdue tickets with delay by days

                      Working on Build's: 14500 PGSQL: SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wos.ISOVERDUE AS "Overdue Status", ad.ORG_NAME AS "Account", CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since ...

                    • Query to show Created, Pending and Completed Requests ( PGSQL & MSSQL )

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT count(wo.workorderid) "Total Created Last Week", count(case when std.ispending='1' THEN 1 ELSE NULL END) "Pending from Last Week", count(case when wo.completedtime >= ...

                    • Request aging with recent worklog comments (MSSQL)

                      Tested in MSSQL build (14306) SELECT wo.WORKORDERID AS "Request ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type",dpt.DEPTNAME AS "Department",pd.PRIORITYNAME AS "Priority", wo.TITLE AS ...

                    • Difference between resolved and Closed notification

                      The requester notifications are sent for two status: 'Resolved' and 'Closed' ​ Both 'Resolved' and 'Closed' are default completed statuses. When a technician provides a solution that he thinks will solve the issue, he/she can set the request to the ...

                    • Add Approvals to request when all the associated tasks are completed.

                      Requirement: Request approvals have to be added only after all the associated tasks of a request are completed. If the approvals are added via the request workflow, then it would be automatically triggered when a request is created. To avoid this, we ...

                      Related Products