Capstone Workflows

SLA Report

Derive Ages and Breaches

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.

sla_breach_capstone.sql 
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 (, '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 (, '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;
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.