Query: Linked ID created date
We have this query to generate request based on their created time. It also includes all the request linked to the request. How can we generate a report that would only include the linked request that is based on their created time? Only request created within a month will be generated the same as the linked ID.
Ex.
ID#1 (created date: 10-24-2019) linked with ID#2 (created date: 10-26-2019) & ID#3 (created date: 11-03-2019)
Only ID#1 and ID#2 will be included in the report if the Created Time parameter is set to 10-01-2019 to 10-31-2019
SD version: 9.3 Build 9333
PostGres SQL
- SELECT wof.UDF_CHAR77 AS "Warranty Status", case when wo.CREATEDTIME !=0 then to_char(to_timestamp(wo.CREATEDTIME/1000), 'DD-Mon-YYYY') else null end "Created Date", case when wo.RESOLVEDTIME !=0 then to_char(to_timestamp(wo.RESOLVEDTIME/1000),
'DD-Mon-YYYY') else null end "Resolved Date",case when wof.UDF_DATE7
!=0 then to_char(to_timestamp(wof.UDF_DATE7/1000), 'DD-Mon-YYYY') else
null end AS "Service Date",wo.WORKORDERID AS "Request ID",
wo.TITLE AS "Subject",
w.description AS "Linked ID",
rrs.RESOLUTION AS "Resolution/Remarks",scf.GUDF_CHAR39 AS "Ref. Invoice/Warranty Card", scf.GUDF_CHAR16 AS "Scope of Service", wmf.UDF_CHAR72 AS "Components/Parts/Fittings", wof.UDF_CHAR88 AS "Cost of Components/Parts", wof.UDF_CHAR87 AS "Cost of Installation Requirement", wof.UDF_CHAR70 AS "Service & Labor/Mobilization Charge", wof.UDF_CHAR73 AS "Transportation Allowance", wof.UDF_DOUBLE1 AS "Total Charges:",wof.UDF_CHAR25 AS "Branch/Location", wof.UDF_CHAR85 AS "Customer Expert" FROM WorkOrder wo
left join WorkOrderHistory woh on woh.workorderid=wo.workorderid
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
left join
(select woh.workorderid ,woh.operation,woh.description from WorkOrderHistory woh where woh.operation='LINKREQUESTTO')w on wo.workorderid=w.workorderid
LEFT JOIN RequestResolver rrr ON wo.WORKORDERID=rrr.REQUESTID LEFT JOIN ApprovalStatusDefinition appStDef ON wos.APPR_STATUSID=appStDef.STATUSID LEFT JOIN RequestResolution rrs ON rrr.REQUESTID=rrs.REQUESTID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID LEFT JOIN ServiceCatalog_Fields scf ON wo.WORKORDERID=scf.WORKORDERID LEFT JOIN WorkOrder_Multi_Fields wmf ON wo.WORKORDERID=wmf.WORKORDERID
WHERE ((((((wof.UDF_CHAR77 = 'Out of Warranty') OR (wof.UDF_CHAR77 = 'Under Warranty')) OR (wof.UDF_CHAR77 = 'Void Warranty')) OR (wof.UDF_CHAR77::TEXT IS NULL)) AND (appStDef.STATUSNAME = 'Approved') AND (serdef.NAME = 'Service Center (Product Service)')) AND (ti.FIRST_NAME = 'SRVC Emerson Macayaon' OR ti.FIRST_NAME = 'SRVC Joey Benedicto')) AND (((wo.CREATEDTIME >= DATETOLONG('2019-11-01 00:00:00')) AND ((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL))) AND ((wo.CREATEDTIME <= DATETOLONG('2019-11-30 23:59:00')) AND (((wo.CREATEDTIME != 0) AND (wo.CREATEDTIME IS NOT NULL)) AND (wo.CREATEDTIME != -1)))) AND wo.ISPARENT='1'
GROUP BY wof.udf_char77, wof.udf_date7,wo.WORKORDERID, scf.gudf_char16, wmf.udf_char72, wof.udf_char88, wof.udf_char87, wof.udf_char70, wof.udf_char73, wof.udf_char76,wof.UDF_DOUBLE1,w.operation,w.description,rrs.resolution,wof.udf_char25,wof.udf_char85,scf.gudf_char39 ORDER BY 1,2,4,5 NULLS FIRST