Date range in query report help
I have a couple query reports that I've created to show WOs that have been worked and WOs that haven't yet been touched. Management wants the date range for them to be: one for this week, and one for last week. They intend to run the report on Thursdays. So the current week report needs to show from last Thursday to the current day, and the previous week report needs to show from the Thursday before, to last Thursday.
<from_thisweek>+259200 doesn't seem to be returning the correct date?
Here's my query thus far:
<from_thisweek>+259200 doesn't seem to be returning the correct date?
Here's my query thus far:
SELECT wo.WORKORDERID 'Request ID',rctd.FIRST_NAME 'Assigned To',DATE_FORMAT(FROM_UNIXTIME(rc.DATETIME/1000) ,'%m-%d-%Y %k:%i') 'Executed Time',sum(ROUND((((rc.MM2COMPLETEREQUEST/1000)/60)))) 'Time Spent', wo.TITLE 'Subject',wof.UDF_CHAR1 'Location',pd.PRIORITYNAME 'Priority',std.STATUSNAME 'Request Status'
FROM WorkOrder_Threaded wot
INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID
INNER JOIN RequestCharges rc ON wo.WORKORDERID=rc.WORKORDERID
LEFT JOIN RCTechnicianIDs rct ON rc.REQUESTCHARGEID=rct.REQUESTCHARGEID
LEFT JOIN SDUser rcti ON rct.TECHNICIANID=rcti.USERID
LEFT JOIN AaaUser rctd ON wos.OWNERID=rctd.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 PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID
WHERE ((( qd.QUEUENAME = 'Live Team') AND ((rc.EXECUTEDTIME >= (<from_thisweek>+259200)) AND ((rc.EXECUTEDTIME != 0) AND (rc.EXECUTEDTIME IS NOT NULL)))) AND ((rc.EXECUTEDTIME <= (<to_thisweek>+259200)) AND ((rc.EXECUTEDTIME != 0) AND (rc.EXECUTEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID
GROUP BY wo.WORKORDERID
ORDER BY 2