SELECT wo.WORKORDERID "Request ID",
max(aau.FIRST_NAME) "Requester",
max(wo.TITLE) "Subject",
max(qd.QUEUENAME) "Group",
max(ti.FIRST_NAME) "Assigned Technician",
MAX(cast((ct.TIMESPENT)/1000 * interval '1 second' as varchar)) "Time Spent (In HH:MM:SS)",
Longtodate(max(ct.TS_STARTTIME)) "Time Spent Starttime",
max(rctd.FIRST_NAME) "Time Spent Technician",
max(ct.DESCRIPTION) "Time Spent Description",
max(std.STATUSNAME) "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
LEFT JOIN SDUser rcti ON ct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON rcti.USERID=rctd.USER_ID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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
WHERE (wo.ISPARENT='1')
AND ct.chargeid=
(SELECT max(chargeid)
FROM workordertocharge
WHERE workorderid=wo.workorderid)
AND wo.createdtime >= <from_thisweek>
AND wo.createdtime <= <to_thisweek>
GROUP BY wo.WORKORDERID
How to compare date column with auto filled date templates?
1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
o <from_thisweek> - Starting date of this week
o <to_thisweek> - Ending date of this week
2. Available Date Templates
o Today - <from_today> - <to_today>
o This week - <from_thisweek> - <to_thisweek>
o Last week - <from_lastweek> - <to_lastweek>
o This month - <from_thismonth> - <to_thismonth>
o Last month - <from_lastmonth> - <to_lastmonth>
o This quarter - <from_thisquarter> - <to_thisquarter>
o Last quarter - <from_lastquarter> - <to_lastquarter>
o Yesterday - <from_yesterday> - <to_yesterday>