SLA Report - SQL Programming

A service report can derive ticket age from fixed dates and filter rows that breach an allowed age.
Program

Play the script to choose the allowed number of days and inspect the SLA breach report.
                            max_days
                            max_days = 3 (default)max_days = 1
                        

                            sla_breach_capstone.sql
                        
Replay: real traced execution (multi-file project)

                            CREATE TABLE tickets (id INTEGER, priority TEXT, opened_day TEXT, closed_day TEXT);
INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);
WITH params(max_days, as_of_day) AS (VALUES (3, '2026-01-06')), aged AS (SELECT id, priority, CAST(julianday(coalesce(closed_day, (SELECT as_of_day FROM params))) - julianday(opened_day) AS INTEGER) AS days_open FROM tickets), breaches AS (SELECT id, priority, days_open, CASE WHEN days_open > (SELECT max_days FROM params) THEN 'breach' ELSE 'ok' END AS sla_status FROM aged) SELECT id, priority, days_open, sla_status FROM breaches WHERE sla_status = 'breach' ORDER BY id;
CREATE TABLE tickets (id INTEGER, priority TEXT, opened_day TEXT, closed_day TEXT);
INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);
WITH params(max_days, as_of_day) AS (VALUES (1, '2026-01-06')), aged AS (SELECT id, priority, CAST(julianday(coalesce(closed_day, (SELECT as_of_day FROM params))) - julianday(opened_day) AS INTEGER) AS days_open FROM tickets), breaches AS (SELECT id, priority, days_open, CASE WHEN days_open > (SELECT max_days FROM params) THEN 'breach' ELSE 'ok' END AS sla_status FROM aged) SELECT id, priority, days_open, sla_status FROM breaches WHERE sla_status = 'breach' ORDER BY id;

                        
1tables ← 1 row1CREATE TABLE tickets (id INTEGER, priority TEXT, opened_day TEXT, closed_day TEXT);2INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);⋯values this step1 rowtables
2tickets ← 4 rows1CREATE TABLE tickets (id INTEGER, priority TEXT, opened_day TEXT, closed_day TEXT);2INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);3WITH params(max_days, as_of_day) AS (VALUES (3, '2026-01-06')), aged AS (SELECT id, priority, CAST(julianday(coalesce(closed_day, (SELECT as_of_day FROM params))) - julianday(opened_day) AS INTEGER) AS days_open FROM tickets), breaches AS (SELECT id, priority, days_open, CASE WHEN days_open > (SELECT max_days FROM params) THEN 'breach' ELSE 'ok' END AS sla_status FROM aged) SELECT id, priority, days_open, sla_status FROM breaches WHERE sla_status = 'breach' ORDER BY id;values this step4 rowstickets
3result ← 2 rows⋯2INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);3WITH params(max_days, as_of_day) AS (VALUES (3, '2026-01-06')), aged AS (SELECT id, priority, CAST(julianday(coalesce(closed_day, (SELECT as_of_day FROM params))) - julianday(opened_day) AS INTEGER) AS days_open FROM tickets), breaches AS (SELECT id, priority, days_open, CASE WHEN days_open > (SELECT max_days FROM params) THEN 'breach' ELSE 'ok' END AS sla_status FROM aged) SELECT id, priority, days_open, sla_status FROM breaches WHERE sla_status = 'breach' ORDER BY id;values this step2 rowsresult
1tables ← 1 row1CREATE TABLE tickets (id INTEGER, priority TEXT, opened_day TEXT, closed_day TEXT);2INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);⋯values this step1 rowtables
2tickets ← 4 rows1CREATE TABLE tickets (id INTEGER, priority TEXT, opened_day TEXT, closed_day TEXT);2INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);3WITH params(max_days, as_of_day) AS (VALUES (1, '2026-01-06')), aged AS (SELECT id, priority, CAST(julianday(coalesce(closed_day, (SELECT as_of_day FROM params))) - julianday(opened_day) AS INTEGER) AS days_open FROM tickets), breaches AS (SELECT id, priority, days_open, CASE WHEN days_open > (SELECT max_days FROM params) THEN 'breach' ELSE 'ok' END AS sla_status FROM aged) SELECT id, priority, days_open, sla_status FROM breaches WHERE sla_status = 'breach' ORDER BY id;values this step4 rowstickets
3result ← 3 rows⋯2INSERT INTO tickets VALUES (1, 'high', '2026-01-01', '2026-01-02'), (2, 'high', '2026-01-01', '2026-01-05'), (3, 'normal', '2026-01-02', '2026-01-06'), (4, 'normal', '2026-01-03', NULL);3WITH params(max_days, as_of_day) AS (VALUES (1, '2026-01-06')), aged AS (SELECT id, priority, CAST(julianday(coalesce(closed_day, (SELECT as_of_day FROM params))) - julianday(opened_day) AS INTEGER) AS days_open FROM tickets), breaches AS (SELECT id, priority, days_open, CASE WHEN days_open > (SELECT max_days FROM params) THEN 'breach' ELSE 'ok' END AS sla_status FROM aged) SELECT id, priority, days_open, sla_status FROM breaches WHERE sla_status = 'breach' ORDER BY id;values this step3 rowsresult
fixed as-of date Open tickets use the literal `as_of_day`, keeping replay deterministic.
derived age `julianday` differences produce a stable days-open value.
breach filter The selector changes the allowed age while preserving the report shape.