Report for requests with no worklog entries in past 24 hours
I'm trying to build a report that provides a list of all tickets, not status 'closed', with a Time Spent End Time greater than 24 hours ago. Ideally it would be filtered by a technician's name.
The required columns would be the RequestID, Requester, Account, Subject, Request Status, Category, Subcategory, Item, Created Time, Time Spent End Time, Technician
When I build a report using the timespent module and report wizard I get a line for every worklog entry in the request, I just need one line per request which is older than 24 hours. I'm assuming this means I need a query report. The custom report's query output is:
SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ad.ORG_NAME AS "Account", wo.TITLE AS "Subject", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", ct.TS_ENDTIME AS "Time Spent Endtime", ti.FIRST_NAME AS "Assigned Technician" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id WHERE (((((std.STATUSNAME::TEXT != 'Closed'::TEXT) AND (std.STATUSNAME::TEXT != 'To Be Invoiced'::TEXT)) AND (std.STATUSNAME::TEXT != 'zDelete'::TEXT)) OR (std.STATUSNAME::TEXT IS NULL)) AND (ti.FIRST_NAME = 'Rebecca Jones')) AND wo.ISPARENT='1' ORDER BY 9 NULLS FIRST
How can I change this so I get only one line per request ID?
Thanks for any help you can provide!
SDP MSP Version: 9.4 Build 9406
The required columns would be the RequestID, Requester, Account, Subject, Request Status, Category, Subcategory, Item, Created Time, Time Spent End Time, Technician
When I build a report using the timespent module and report wizard I get a line for every worklog entry in the request, I just need one line per request which is older than 24 hours. I'm assuming this means I need a query report. The custom report's query output is:
SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ad.ORG_NAME AS "Account", wo.TITLE AS "Subject", std.STATUSNAME AS "Request Status", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", ct.TS_ENDTIME AS "Time Spent Endtime", ti.FIRST_NAME AS "Assigned Technician" FROM WorkOrder wo LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID 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 ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID INNER JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid INNER JOIN AccountDefinition ad ON asm.accountid=ad.org_id WHERE (((((std.STATUSNAME::TEXT != 'Closed'::TEXT) AND (std.STATUSNAME::TEXT != 'To Be Invoiced'::TEXT)) AND (std.STATUSNAME::TEXT != 'zDelete'::TEXT)) OR (std.STATUSNAME::TEXT IS NULL)) AND (ti.FIRST_NAME = 'Rebecca Jones')) AND wo.ISPARENT='1' ORDER BY 9 NULLS FIRST
How can I change this so I get only one line per request ID?
Thanks for any help you can provide!
SDP MSP Version: 9.4 Build 9406