Query to show open requests where no worklog is added for the past 2 days

Please go to Reports-New Query Report and execute this report.
SELECT wo.WORKORDERID “Request ID”,LONGTODATE(wo.createdtime) “Created Date”,qd.QUEUENAME “Group”,ti.FIRST_NAME “Technician”,wo.title “Subject”, std.STATUSNAME “Request Status”,sdd.name “Site”, accountdefinition.org_name “Account”  FROM WorkOrder wo 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 
LEFT JOIN sitedefinition sdef ON wo.siteid=sdef.siteid
LEFT JOIN sdorganization sdd ON wo.siteid=sdd.org_id 
LEFT JOIN accountsitemapping asm ON sdef.siteid=asm.siteid 
LEFT JOIN accountdefinition ON accountdefinition.org_id=asm.accountid
WHERE (wo.ISPARENT=’1′) and wo.workorderid not in(select wtc.workorderid from WorkOrderToCharge wtc left join chargestable ct on ct.chargeid=wtc.chargeid where dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (ct.createdtime/1000),’1970-01-01 00:00:00′)>= dateadd(dd, -2, getdate())) and std.ispending=’1′ 
order by 1
This works in MSSQL
This entry was posted in Reports.

Need More Help?

Are you looking for a personalized customer support? Submit your question or Call our Toll Free Number to speak with ServiceDesk Plus MSP Expert who can offer a better solution for your requirements.

Submit a Ticket
Email : sdpmsp@manageengine.com
Toll Free : 1-888-720-9500