SELECT

    wo.WORKORDERID AS "Request ID",

    aau.FIRST_NAME AS "Requester",

    dpt.DEPTNAME AS "Department",

    wo.TITLE AS "Subject",

    ti.FIRST_NAME AS "Technician",

    std.STATUSNAME AS "Request Status",

    LONGTODATE(wo.CREATEDTIME) AS "Created Time",

    asd.STAGENAME AS "Stage Name",

    LONGTODATE(ApprovalStage.SENT_DATE) AS "Approval Sent Date",

    LONGTODATE(ApprovalDetails.ACTION_DATE) AS "Approved Date",

    ApprovalStatusDefinition.STATUSNAME AS "Approval Status",

    ApprovalDetails.EMAIL AS "Approver Email"

FROM WorkOrder wo

-- Requester Info

LEFT JOIN SDUser sdu ON wo.REQUESTERID = sdu.USERID

LEFT JOIN AaaUser aau ON sdu.USERID = aau.USER_ID

-- Department

LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID = dpt.DEPTID

-- Work Order Status / Technician

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

-- Approval Mapping and Status

JOIN ApprovalStageMapping asm ON wo.WORKORDERID = asm.WORKORDERID

LEFT JOIN ApprovalDetails ON asm.APPROVAL_STAGEID = ApprovalDetails.APPROVAL_STAGEID

LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID = ApprovalStage.APPROVAL_STAGEID

LEFT JOIN ApprovalStatusDefinition ON ApprovalDetails.STATUSID = ApprovalStatusDefinition.STATUSID

LEFT JOIN ApprovalStageDefinition asd ON asd.STAGEID = ApprovalDetails.STAGEID

-- Filters

WHERE

    wo.ISPARENT = '1'

    AND ApprovalStatusDefinition.STATUSNAME LIKE '%Pending%'