SQL Query to export request details

SQL Query to export request details


Supported only for SQL DB(14201 build).
Executed in SQL DB  directly to export the requests.

Below query to export requests for one account / site.

SELECT "wo"."WORKORDERID" AS "Request ID", "wo"."TITLE" AS "Subject", "wotodesc"."FULLDESCRIPTION" AS "Description", "aau"."FIRST_NAME" AS "Requester", "rrs"."RESOLUTION" AS "Resolution", "ad"."ORG_NAME" AS "Account", "sdo"."NAME" AS "Site", "qd"."QUEUENAME" AS "Group", "ti"."FIRST_NAME" AS "Technician", "std"."STATUSNAME" AS "Request Status", "pd"."PRIORITYNAME" AS "Priority", "lvd"."LEVELNAME" AS "Level", "mdd"."MODENAME" AS "Request Mode", "urgdef"."NAME" AS "Urgency", "impdef"."NAME" AS "Impact", "rtdef"."NAME" AS "Request Type", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", "icd"."NAME" AS "Item",format(CAST(DATEADD(second,("wo"."CREATEDTIME")/1000,'1970-01-01 00:00:00') AS datetime),'dd MMM yyyy, HH:mm:ss','en-us') AS "Created Time",case when ("wo"."RESOLVEDTIME") > 0 then format(CAST(DATEADD(second,("wo"."RESOLVEDTIME")/1000,'1970-01-01 00:00:00') AS datetime),'dd MMM yyyy, HH:mm:ss','en-us') ELSE null END AS "Resolved Time",case when ("wo"."COMPLETEDTIME") > 0 then format(CAST(DATEADD(second,("wo"."COMPLETEDTIME")/1000,'1970-01-01 00:00:00') AS datetime),'dd MMM yyyy, HH:mm:ss','en-us') else null END AS "Completed Time"  FROM "WorkOrder" "wo" LEFT JOIN "WorkOrderAccountMapping" "wam" ON "wo"."WORKORDERID"="wam"."WORKORDERID" LEFT JOIN "PortalAccounts" "port_acc" ON "wam"."ACCOUNTID"="port_acc"."ACCOUNTID" LEFT JOIN "AccountDefinition" "ad" ON "port_acc"."ACCOUNTID"="ad"."ORG_ID" LEFT JOIN "ModeDefinition" "mdd" ON "wo"."MODEID"="mdd"."MODEID" 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 "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID" 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 "LevelDefinition" "lvd" ON "wos"."LEVELID"="lvd"."LEVELID" LEFT JOIN "SubCategoryDefinition" "scd" ON "wos"."SUBCATEGORYID"="scd"."SUBCATEGORYID" LEFT JOIN "ItemDefinition" "icd" ON "wos"."ITEMID"="icd"."ITEMID" LEFT JOIN "UrgencyDefinition" "urgdef" ON "wos"."URGENCYID"="urgdef"."URGENCYID" LEFT JOIN "ImpactDefinition" "impdef" ON "wos"."IMPACTID"="impdef"."IMPACTID" LEFT JOIN "RequestTypeDefinition" "rtdef" ON "wos"."REQUESTTYPEID"="rtdef"."REQUESTTYPEID" LEFT JOIN "RequestResolver" "rrr" ON "wo"."WORKORDERID"="rrr"."REQUESTID" LEFT JOIN "RequestResolution" "rrs" ON "rrr"."REQUESTID"="rrs"."REQUESTID" LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID" LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID" LEFT JOIN "WorkOrderToDescription" "wotodesc" ON "wo"."WORKORDERID"="wotodesc"."WORKORDERID" LEFT JOIN "SiteDefinition" "siteDef" ON "wo"."SITEID"="siteDef"."SITEID" LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID" WHERE  ( "ad"."ORG_NAME" = 'My Org Inc' ) and ("sdo"."NAME"='Common Site')  AND wo.ISPARENT='1' and dateadd(s,datediff(s,GETUTCDATE(),getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') >= convert(varchar,'2023-01-01 00:00',21) and dateadd(s,datediff(s,GETUTCDATE(),getdate()) + (wo.createdtime/1000),'1970-01-01 00:00:00') <= convert(varchar,'2023-07-01 00:00',21)


==
AND ("sdo"."NAME"='Common Site')  - remove this if you need to take all My Org Inc records.

                    New to ADSelfService Plus?

                      • Related Articles

                      • Query to retrieve the requests details

                        Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...
                      • Query for request attachment details (MSSQL & PGSQL)

                        Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...
                      • Query to retrieve both the live and archived requests details

                        Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...
                      • Query to show parent and child requests (Linked Request) and its details.(MSSQL & PGSQL)

                        Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute this query. select woparent.WORKORDERID "Parent request ID", aaauparentreq.first_name "Parent Requester", woparent.title "Parent Subject", ...
                      • Query to retrieve the aging days details of requests

                        Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", req.FIRST_NAME AS "Requester", LONGTODATE(wo.CREATEDTIME) AS "Created Date", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date", (FLOOR(wo.TIMESPENTONREQ / (1000 * 60 * 60 * ...