FEETN
Flag and fix some CP effort errors
For the methods RLP, CP, EP, FP and FN, the field effort_total_num is used for the “Number of pot/trap lifts in the day” and effort_num is used for the “Number of pots/traps in the water at midnight”. For the CP method, these fields are checked for consistency. Records are flagged where the number of pots lifted was more than 9 times the number left overnight and the number of pots lifted was more than 100. Where effort_total_num is missing and effort_num is greater than zero, or if effort_num > effort_total_num, effort_total_num is replaced with effort_num.
-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('FEETN', 'edw', 'effort', 'Fix', 'Flag and fix some CP effort errors');
CREATE OR REPLACE FUNCTION FEETN()
RETURNS VOID AS $$
UPDATE groomed.effort SET checks = checks || '{FEETN}'
WHERE primary_method IN ('CP', 'RLP', 'EP', 'FP', 'FN') AND effort_total_num > effort_num * 9 OR effort_total_num > 100;
UPDATE groomed.effort SET checks = checks || '{FEETN}', effort_total_num = effort_num
WHERE effort_total_num IS NULL AND primary_method IN ('CP', 'RLP', 'EP', 'FP', 'FN') AND effort_num > 0 OR primary_method IN ('CP', 'RLP', 'EP', 'FP', 'FN') AND effort_num > effort_total_num;
$$ LANGUAGE SQL;
SELECT FEETN();