SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
wo.TITLE "Subject",
cd.CATEGORYNAME "Category",
ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
(select conversationdescription.description from conversationdescription left join conversation con1 on conversationdescription.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid) "Last requester response",
longtodate((select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(con1.createdtime/1000),'1970-01-01 00:00:00') from conversationdescription left join conversation con1 on conversationdescription.conversationid=con1.conversationid where con1.conversationid in(select max(conversationid) from conversation group by conversation.workorderid) and con1.workorderid=wo.workorderid)) "Last requester response Time",
(select notificationtodesc.description from notificationtodesc left join notification no1 on notificationtodesc.notificationid=no1.notificationid left join notify_workorder nwo1 on no1.notificationid=nwo1.notificationid where no1.notificationid in (select max(notification.notificationid) from notification left join notify_workorder on notification.notificationid=notify_workorder.notificationid group by notify_workorder.workorderid) and nwo1.workorderid=wo.workorderid) "Last Tech reply",
(select dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(no1.notificationdate/1000),'1970-01-01 00:00:00') from notificationtodesc left join notification no1 on notificationtodesc.notificationid=no1.notificationid left join notify_workorder nwo1 on no1.notificationid=nwo1.notificationid where no1.notificationid in (select max(notification.notificationid) from notification left join notify_workorder on notification.notificationid=notify_workorder.notificationid group by notify_workorder.workorderid) and nwo1.workorderid=wo.workorderid) "Last Tech reply Time" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE std.ispending='true' and wo.CREATEDTIME >= <from_thisweek> AND wo.CREATEDTIME <= <to_thisweek>
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
- This year - <from_thisyear> - <to_thisyear>
- Last year - <from_lastyear> - <to_lastyear>