My first query report - help!
The Completed Requests By Date by Technician is a report we're interested in, but the weekly timescale is no use, we need it by month. No problem I think, a minor query modification.
SELECT ti.FIRST_NAME 'Technician',wo.WORKORDERID 'Request ID',wo.COMPLETEDTIME 'Completed Time' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID 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 (((std.STATUSNAME = 'Closed') AND ((wo.COMPLETEDTIME >= <from_thisweek>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND ((wo.COMPLETEDTIME <= <to_thisweek>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 3
So looking at this I decide that from_thisweek and to_thisweek to from_thismonth and to_thismonth. Nicely done ;)
However, the output format does not match that of the original report. I can generate another via the custom report option, but the query it generates looks like it is using fixed dates, even when I specify thismonth as the range.
SELECT ti.FIRST_NAME 'Technician',ti.FIRST_NAME 'Technician',wo.COMPLETEDTIME 'Completed Time' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID 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 WHERE (((wo.COMPLETEDTIME >= $P{fromdate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <= $P{todate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 3
How can I accomplish the Completed Requests By Date by Technician in a month range which chooses whichever month we are in as the range?
SELECT ti.FIRST_NAME 'Technician',wo.WORKORDERID 'Request ID',wo.COMPLETEDTIME 'Completed Time' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID 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 (((std.STATUSNAME = 'Closed') AND ((wo.COMPLETEDTIME >= <from_thisweek>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND ((wo.COMPLETEDTIME <= <to_thisweek>) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 3
So looking at this I decide that from_thisweek and to_thisweek to from_thismonth and to_thismonth. Nicely done ;)
However, the output format does not match that of the original report. I can generate another via the custom report option, but the query it generates looks like it is using fixed dates, even when I specify thismonth as the range.
SELECT ti.FIRST_NAME 'Technician',ti.FIRST_NAME 'Technician',wo.COMPLETEDTIME 'Completed Time' FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID 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 WHERE (((wo.COMPLETEDTIME >= $P{fromdate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL))) AND ((wo.COMPLETEDTIME <= $P{todate}) AND ((wo.COMPLETEDTIME != 0) AND (wo.COMPLETEDTIME IS NOT NULL)))) AND wot.THD_WOID=wot.WORKORDERID ORDER BY 3
How can I accomplish the Completed Requests By Date by Technician in a month range which chooses whichever month we are in as the range?