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. 

          • Related Articles

          • Archived and Active Request in a same report

            This Report is used to get the complete list of request both active and archived request in the same report.  To make any changes to a query, refer to the KB article below. ...
          • How to close associated incident requests of change request automatically

            This post describes the use of a python script to close change that are in a specific stage using Custom Schedules. Use Case: There may be a scenario where number of Change Requests that have got closed and the associated incident is still in open ...
          • How to set due by time in a request with value from a date time additional field.

            This python script is used to set the due by date in the request based on the value that is set in a date additional field.This is performed using apis for updating a request. UseCase: Request due by time will be set based on Incident / Service ...
          • Request Dependency

            SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician", pd.PRIORITYNAME "Priority", wo.CREATEDTIME "Created Time", wo.DUEBYTIME "DueBy Time", std.STATUSNAME "Request Status", CASE WHEN wos.ISOVERDUE='1' THEN 'Yes' ELSE ...
          • Request recipient email

            This report is used to find the recipient email address.  Some requests are forward to others through the mail, this report helps to find the statistics how many requests are forward to others using the email address filter.  To make any changes to a ...