LASCF

Correct some state codes

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LASCF', 'edw', 'landings', 'Fix', 'Correct some state codes');

CREATE OR REPLACE FUNCTION LASCF() RETURNS VOID AS $$

INSERT INTO groomed.checks(code, "table", "column", id, orig, new)
SELECT 'LASCF', 'landings', 'state_code', id, state_code,
       CASE WHEN state_code IN ('EAT', 'DIS') THEN 'GRE'
            WHEN state_code IN ('HED') THEN 'HDS'
            WHEN state_code IN ('TGU') THEN 'HGU'
--            WHEN state_code IN ('GGO', 'GGT') THEN 'GGU'
            ELSE NULL
       END
FROM   groomed.landings
WHERE  state_code IN ('EAT','DIS','HED','TGU');
--,'GGO','GGT');

UPDATE groomed.landings
SET    checks = array_append(checks,'LASCF'),
       state_code = u.new
FROM   groomed.checks u
WHERE  u.code = 'LASCF'
AND    groomed.landings.id = u.id
AND    groomed.landings.state_code = u.orig;

$$ LANGUAGE SQL VOLATILE;

SELECT LASCF();

Effects of grooming

Figure 1: The number of records affected by the LASCF rule, by year and source.

Figure 2: The proportion of records affected by the LASCF rule, by form type, for all form types where some records are groomed by the LASCF rule.