How to Identify Where a Ticket is Assigned to at the Point of Breaching SLA

@ChrisButters Here is example code for all events for SQL Server/Azure SQL:

WITH inc as (SELECT
    incident.incident_id,
    (CASE WHEN inc_data.u_date1 IS NOT NULL THEN inc_data.earliest_sla_resolve_due ELSE incident.inc_resolve_due END) breach_date, --Due/breach date. This takes into account stopped clocks
    (SELECT TOP 1 act_reg.date_actioned FROM act_reg WHERE act_reg.incident_id = incident.incident_id AND act_reg.act_type_id IN (4, 5) AND act_reg.date_actioned <= (CASE WHEN inc_data.u_date1 IS NOT NULL THEN inc_data.earliest_sla_resolve_due ELSE incident.inc_resolve_due END) ORDER BY act_reg.date_actioned DESC) last_resolve_or_close_date, --before or on the breach date
    (SELECT TOP 1 act_reg.date_actioned FROM act_reg WHERE act_reg.incident_id = incident.incident_id AND act_reg.act_type_id = 6 AND act_reg.date_actioned <= (CASE WHEN inc_data.u_date1 IS NOT NULL THEN inc_data.earliest_sla_resolve_due ELSE incident.inc_resolve_due END) ORDER BY act_reg.date_actioned DESC) last_reopen_date --before or on the breach date
FROM
    incident
    INNER JOIN inc_data ON incident.incident_id = inc_data.incident_id
) --All events (modify as required)
SELECT
    inc.incident_id,
    (CASE WHEN inc.breach_date is not null --Has a breach date
        AND
        inc.breach_date <= getDate() --Before now else it hasn't breached yet
        AND (
            (last_reopen_date IS NULL AND last_resolve_or_close_date IS NULL) --Never closed or resolved or reopened by the breach date
            OR
            (last_resolve_or_close_date < last_reopen_date) --Reopened
        ) --Is open on breach date
        THEN
        (SELECT TOP 1 act_reg.ass_svd_id FROM act_reg WHERE act_reg.incident_id = inc.incident_id AND act_reg.act_type_id = 1 AND act_reg.date_actioned <= inc.breach_date ORDER BY act_reg.date_actioned DESC) --Last assignment before or on the breach date
    ELSE NULL END) svd_id_at_breach
FROM
    inc

If you are using Oracle you need to modify all the subqueries with “TOP 1”s  with a nested subquery and use rownum = 1:

e.g.:  (SELECT TOP 1 act_reg.ass_svd_id FROM act_reg WHERE act_reg.incident_id = inc.incident_id AND act_reg.act_type_id = 1 AND act_reg.date_actioned <= inc.breach_date ORDER BY act_reg.date_actioned DESC)

becomes:

(SELECT * FROM (SELECT act_reg.ass_svd_id FROM act_reg WHERE act_reg.incident_id = inc.incident_id AND act_reg.act_type_id = 1 AND act_reg.date_actioned <= inc.breach_date ORDER BY act_reg.date_actioned DESC) WHERE rownum = 1)

Also you will need to replace getDate() with SYSDATE

Paul Cooper

Software Engineer/Reports Designer