Hi
We are running SDP MSP 8.3 8310 with a postgres database.
I have borrowed this query report from another thread that I found here, but would like to add additional fields and a sort order. I have tried doing it myself but it keeps failing!
SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status", wo.TITLE "Subject", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wos.LAST_TECH_UPDATE) "Last Updated Time", (case when ((date(current_date) - date(to_timestamp(wo.CREATEDTIME/1000))) >= 7 and (date(current_date) - date(to_timestamp(wo.CREATEDTIME/1000))) <30) THEN '7-30 days' WHEN ((date(current_date) - date(to_timestamp(wo.CREATEDTIME/1000))) >= 30 and (date(current_date) - date(to_timestamp(wo.CREATEDTIME/1000))) <90) THEN '30-90 days' else ' Greater Than 90 days' end) "Period" FROM WorkOrder wo 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 ((std.STATUSNAME NOT LIKE '%Closed%' ) AND (std.STATUSNAME NOT LIKE '%Resolved%' )) and ((date(current_date) - date(to_timestamp(wo.CREATEDTIME/1000))) >= 7)
I would like to add:
Account (if possible)
Site
Requester
Category
Request Type
Is it possible to sort by create date or request ID as well?
Thanks so much!