Query to show who actually violated the SLA (PGSQL & MSSQL )

Query to show who actually violated the SLA (PGSQL & MSSQL )

Working on Builds 14500 and above.
Tested in build PGSQL (14300) and MSSQL (14306)

Databases: PGSQL & MSSQL:

SELECT wo.WORKORDERID "Request ID",
       max(wo.TITLE) "Subject",
       max(ti.FIRST_NAME) "Current Technician",
       max(std.STATUSNAME) "Request Status",
       CASE
           WHEN max(tech.first_name) IS NOT NULL THEN max(tech.first_name)
           ELSE max(ti.FIRST_NAME)
       END "Due by Technician" 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
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN
  (SELECT woh1.workorderid,
          ti1.first_name FROM workorderhistory woh1
   LEFT JOIN workorderhistorydiff wohd1 ON woh1.historyid=wohd1.historyid
   LEFT JOIN aaauser ti1 ON cast(cast(wohd1.prev_value AS varchar) AS int)=ti1.user_id
   LEFT JOIN workorder wo1 ON wo1.workorderid=woh1.workorderid
   WHERE (wohd1.COLUMNNAME) IN ('OWNERID')
     AND woh1.operationtime > wo1.duebytime) tech ON wo.workorderid=tech.workorderid
WHERE wos.isoverdue='1'
  AND wo.CREATEDTIME >= <from_thismonth>
  AND wo.CREATEDTIME <= <to_thismonth> GROUP  BY wo.WORKORDERID

NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.

How to compare date column with auto filled date templates?
  1. Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
    • <from_thisweek> - Starting date of this week
    • <to_thisweek> - Ending date of this week
  2. Available Date Templates
    • Today - <from_today> - <to_today>
    • This week - <from_thisweek> - <to_thisweek>
    • Last week - <from_lastweek> - <to_lastweek>
    • This month - <from_thismonth> - <to_thismonth>
    • Last month - <from_lastmonth> - <to_lastmonth>
    • This quarter - <from_thisquarter> - <to_thisquarter>
    • Last quarter - <from_lastquarter> - <to_lastquarter>
    • Yesterday - <from_yesterday> - <to_yesterday>

                  New to ADSelfService Plus?