Query to get Request details with requester and Notes details

Query to get Request details with requester and Notes details

Version : 13001
DB : PGSQL & MSSQL

OUTPUT :




SELECT "wo"."WORKORDERID" AS "Request ID",
"aau"."FIRST_NAME" AS "Requester",
"wo"."TITLE" AS "Subject",
"ti"."FIRST_NAME" AS "Technician",
"pd"."PRIORITYNAME" AS "Priority",
"wotodesc"."FULLDESCRIPTION" AS "Description",
"std"."STATUSNAME" AS "Request Status",
"wo"."CREATEDTIME" AS "Created Time",
(note.NOTESTEXT) "Notes"  FROM "WorkOrder" "wo"
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 "StatusDefinition" "std" ON "wos"."STATUSID"="std"."STATUSID"
LEFT JOIN "PriorityDefinition" "pd" ON "wos"."PRIORITYID"="pd"."PRIORITYID"
LEFT JOIN "WorkOrderToDescription" "wotodesc" ON "wo"."WORKORDERID"="wotodesc"."WORKORDERID"
LEFT JOIN "WorkOrderAccountMapping" "wam" ON "wo"."WORKORDERID"="wam"."WORKORDERID"
LEFT JOIN "PortalAccounts" "port_acc" ON "wam"."ACCOUNTID"="port_acc"."ACCOUNTID"
LEFT JOIN workordernotes wono ON wo.WORKORDERID=wono.WORKORDERID
LEFT JOIN Notes note ON wono.notesid=note.notesid
LEFT JOIN "AccountDefinition" "ad" ON "port_acc"."ACCOUNTID"="ad"."ORG_ID" WHERE  ( ( ( "ad"."ORG_NAME" = N'Community Justice Division' ) AND ( ( ( "wo"."TITLE" NOT LIKE N'%Microsoft Defender%' ESCAPE '\' ) AND ( "wo"."TITLE" NOT LIKE N'%Monthly%' ESCAPE '\' ) ) OR ( "wo"."TITLE" IS NULL ) ) ) AND ( ( ( ( "std"."STATUSNAME" != N'Change Pending' ) AND ( "std"."STATUSNAME" != N'Closed' ) ) AND ( "std"."STATUSNAME" != N'Resolved' ) ) OR ( "std"."STATUSNAME" IS NULL ) ) )  AND wo.ISPARENT='1'

      New to ADSelfService Plus?