audit_history.sql

DROP TABLE IF EXISTS jobs_history;

CREATE TABLE jobs_history
(
    id INTEGER IDENTITY(1, 1) PRIMARY KEY,
    jobId INTEGER NOT NULL,
    valid_from TIMESTAMP NOT NULL,
    valid_to TIMESTAMP NULL,
    type VARCHAR(50),
    status VARCHAR(50),
    source VARCHAR(50),
    name VARCHAR(100)
);

INSERT INTO jobs_history (jobId, valid_from, valid_to, type, status, source, name)
VALUES
    (1, '2021-03-19 00:21:30', NULL, 'Full-Time', 'Active', 'LinkedIn', 'Data Scientist'),
    (2, '2021-03-19 00:21:30', NULL, 'Full-Time', 'Active', 'LinkedIn', 'ML Engineer'),
    (3, '2021-03-19 00:21:30', '2021-03-19 00:21:37', 'Full-Time', 'Active', 'LinkedIn', 'Project Manager'),
    (3, '2021-03-19 00:21:37', '2021-03-19 00:21:42', 'Full-Time', 'On-Hold', 'LinkedIn', 'Project Manager'),
    (3, '2021-03-19 00:21:42', '2021-03-19 00:22:00', 'Full-Time', 'Active', 'LinkedIn', 'Project Manager'),
    (3, '2021-03-19 00:22:00', NULL, 'Full-Time', 'Active', 'Indeed', 'Project Manager');


-- Desired Query Results:
-- History of changes to status column

jobId, status, valid_from, valid_to
1, 'Active', '2021-03-19 00:21:30', NULL
2, 'Active', '2021-03-19 00:21:30', NULL
3, 'Active', '2021-03-19 00:21:30', '2021-03-19 00:21:37'
3, 'On-Hold', '2021-03-19 00:21:37', '2021-03-19 00:21:42'
3, 'Active', '2021-03-19 00:21:42', NULL

query_brainstorming.sql

WITH jobs_status_history AS (
    SELECT jh.id,
           jh.jobId,
           jh.valid_from,
           jh.valid_to,
           jh.status,
           LAG(jh.status, 1) OVER (PARTITION BY jh.jobId ORDER BY jh.valid_from ASC) AS "previous_status"
    FROM jobs_history AS jh
),

job_status_changes AS (
    SELECT id,
           jobId,
           valid_from,
           CASE
               WHEN status = 'Active' AND previous_status IS NOT NULL THEN NULL
               ELSE valid_to
               END AS valid_to,
           status,
           previous_status
    FROM jobs_status_history
    WHERE status != COALESCE(previous_status, 'coalescenullvalues')
)

SELECT
    id,
    jobId,
    valid_from,
    CASE
        WHEN valid_to IS NULL THEN GETDATE()
        ELSE valid_to
    END AS valid_to,
    status
FROM job_status_changes
ORDER BY jobId, id