MSP Report

MSP Report

Can someone help me come up with a report that will be able to tell me how long a request has been overdue?
 
I have configured the report but i want an additional column with the information.
 
It should be able to give the data in the following manner:
 
1. For the completed requests: Completed Time - DueBy Time
2. For the Pending requests: Current Time - DueBy Time
 
The system will perform these small calculations and list the output in the additional column.
 
The MSP details are given below:
Your Version : 8.1 Build 8102
Latest Version
: 8.3 Build 8309 [Details]
 
The additional field should be titled 'Overdue Time' with same format as Completed & DueBy columns.
 
Here is the query of the report:
 
SELECT ti.FIRST_NAME "Technician",wo.WORKORDERID "Request ID",mdd.MODENAME "Request Mode",icd.NAME "Item",wo.TITLE "Subject",cri.FIRST_NAME "Created By",ti.FIRST_NAME "Technician",wotodesc.FULLDESCRIPTION "Description",pd.PRIORITYNAME "Priority",std.STATUSNAME "Request Status",longtodate(wo.CREATEDTIME) "Created Time",longtodate(wo.DUEBYTIME) "DueBy Time",longtodate(wo.COMPLETEDTIME) "Completed Time",wos.ISOVERDUE "Overdue Status" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser crd ON wo.CREATEDBYID=crd.USERID LEFT JOIN AaaUser cri ON crd.USERID=cri.USER_ID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE  (((((qd.QUEUENAME = N'IT - SMO - Service Operations' COLLATE SQL_Latin1_General_CP1_CI_AS) AND (icd.NAME = N'Remington' COLLATE SQL_Latin1_General_CP1_CI_AS)) AND (std.ISPENDING = 1)) AND (((longtodate(wo.CREATEDTIME) >= 1427810400000) AND ((longtodate(wo.CREATEDTIME) != 0) AND (longtodate(wo.CREATEDTIME) IS NOT NULL))) AND ((longtodate(wo.CREATEDTIME) <= 1430402399000) AND (((longtodate(wo.CREATEDTIME) != 0) AND (longtodate(wo.CREATEDTIME) IS NOT NULL)) AND (longtodate(wo.CREATEDTIME) != -1))))) AND (wo.SITEID IN (903,2415,7503,14,312,2406,7504,902,7505,2416,904,6,7,905,2417,7506,7507,2418,906,8,9,907,2419,7508,7509,2420,908,10,909,2421,7510,15,5,7511,2422,910,911,11,2423,7512,7513,2424,12,912,913,13,2425,7514)))  AND wo.ISPARENT='1'  ORDER BY 1, 11, 10
 
Please let me know if there is query out there i can just pick up and use.
 
 
Regards,
Raymond
 
              New to ADManager Plus?

                New to ADSelfService Plus?