Request Management - quick overview of monthly opened, closed and overdue requests

Request Management - quick overview of monthly opened, closed and overdue requests

This report provides a quick overview of monthly opened, closed and overdue requests. It provides you a quick determination of a large number of requests. This information can then be used by a request management administrator to determine if a particular department requires additional resources to efficiently manage the number of requests. 



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


Click this link to navigate to the next 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 Products