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.
To make any changes to a query, refer to the KB article below.
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> UNIONSELECT '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>UNIONSELECT '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 ADSelfService Plus?
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. ...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 ...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 ...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 ...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 ...