Missing Days - SQL Programming

A left join from expected days to actual readings can reveal time-series gaps.
Program

Play the script to choose the first expected day and identify which readings are missing.
                            start_day
                            start_day = '2026-04-01' (default)start_day = '2026-04-02'start_day = '2026-04-03'
                        

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

                            CREATE TABLE expected_days (day TEXT);
INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');
CREATE TABLE readings (day TEXT, temperature INTEGER);
INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);
WITH params(start_day) AS (VALUES ('2026-04-01')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;
CREATE TABLE expected_days (day TEXT);
INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');
CREATE TABLE readings (day TEXT, temperature INTEGER);
INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);
WITH params(start_day) AS (VALUES ('2026-04-02')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;
CREATE TABLE expected_days (day TEXT);
INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');
CREATE TABLE readings (day TEXT, temperature INTEGER);
INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);
WITH params(start_day) AS (VALUES ('2026-04-03')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;

                        
1tables ← 1 row1CREATE TABLE expected_days (day TEXT);2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');⋯values this step1 rowtables
2expected_days ← 4 rows1CREATE TABLE expected_days (day TEXT);2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');3CREATE TABLE readings (day TEXT, temperature INTEGER);⋯values this step4 rowsexpected_days
3tables ← 2 rows⋯2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');3CREATE TABLE readings (day TEXT, temperature INTEGER);4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);⋯values this step2 rowstables
4readings ← 2 rows⋯3CREATE TABLE readings (day TEXT, temperature INTEGER);4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);5WITH params(start_day) AS (VALUES ('2026-04-01')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;values this step2 rowsreadings
5result ← 4 rows⋯4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);5WITH params(start_day) AS (VALUES ('2026-04-01')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;values this step4 rowsresult
1tables ← 1 row1CREATE TABLE expected_days (day TEXT);2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');⋯values this step1 rowtables
2expected_days ← 4 rows1CREATE TABLE expected_days (day TEXT);2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');3CREATE TABLE readings (day TEXT, temperature INTEGER);⋯values this step4 rowsexpected_days
3tables ← 2 rows⋯2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');3CREATE TABLE readings (day TEXT, temperature INTEGER);4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);⋯values this step2 rowstables
4readings ← 2 rows⋯3CREATE TABLE readings (day TEXT, temperature INTEGER);4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);5WITH params(start_day) AS (VALUES ('2026-04-02')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;values this step2 rowsreadings
5result ← 3 rows⋯4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);5WITH params(start_day) AS (VALUES ('2026-04-02')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;values this step3 rowsresult
1tables ← 1 row1CREATE TABLE expected_days (day TEXT);2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');⋯values this step1 rowtables
2expected_days ← 4 rows1CREATE TABLE expected_days (day TEXT);2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');3CREATE TABLE readings (day TEXT, temperature INTEGER);⋯values this step4 rowsexpected_days
3tables ← 2 rows⋯2INSERT INTO expected_days VALUES ('2026-04-01'), ('2026-04-02'), ('2026-04-03'), ('2026-04-04');3CREATE TABLE readings (day TEXT, temperature INTEGER);4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);⋯values this step2 rowstables
4readings ← 2 rows⋯3CREATE TABLE readings (day TEXT, temperature INTEGER);4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);5WITH params(start_day) AS (VALUES ('2026-04-03')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;values this step2 rowsreadings
5result ← 2 rows⋯4INSERT INTO readings VALUES ('2026-04-01', 71), ('2026-04-03', 69);5WITH params(start_day) AS (VALUES ('2026-04-03')) SELECT e.day, CASE WHEN r.day IS NULL THEN 'missing' ELSE 'present' END AS status FROM expected_days AS e LEFT JOIN readings AS r ON r.day = e.day WHERE e.day >= (SELECT start_day FROM params) ORDER BY e.day;values this step2 rowsresult
LEFT JOIN The expected day remains visible even when there is no matching reading.
gap status `CASE` labels missing joined rows as `missing`.
calendar table A small expected-days table gives the query a complete timeline to compare against.