LADUP

Landing duplicated on CLR form

The groomer comment says this: Starr (2007) suggests “Look for duplicate landings on multiple (CELR and CLR) forms. Keep only a single version if determined that the records are duplicated.” If the following fields are duplicated across form types then drop all but the CEL record: vessel_key, landing_datetime, fishstock_code, state_code, destination_type, unit_type, unit_num, unit_weight, green_weight. Do this after state_code, destination_type etc have been fixed up.

Interpreted here in the following way: When multiple landing rows have the same values in all the columns listed above, and at least one of those rows has a CEL form_type, and all the duplicated rows’ form types are either CEL or CLR, then we will flag all but one of the CEL rows in the set to be dropped.

-- © Copyright 2026 The Kahawai Collective
-- SPDX-License-Identifier: MIT
INSERT INTO grooming_rules
VALUES ('LADUP', 'edw', 'landings', 'Drop', 'Duplicate landings');

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

WITH grouped AS (
    select id, row_number() over w, count(*) over w
    from groomed.landings
    window w as (
      partition by vessel_key, landing_datetime, fishstock_code, state_code, destination_type,
        unit_type, unit_num, unit_weight, green_weight
      order by id
      rows between unbounded preceding and unbounded following
    )
), dup_row_form_types AS (
    SELECT id,
           row_number() OVER w,
           form_type,
           first_value(form_type) OVER w AS first_form_type,
           '{CEL,CLR}' @> array_agg(form_type) OVER w AS all_cel_clr
    FROM   groomed.landings
    WHERE  id IN (SELECT id FROM grouped WHERE count > 1)
    WINDOW w AS (
        PARTITION BY vessel_key, landing_datetime, fishstock_code, state_code,
             destination_type, unit_type, unit_num, unit_weight, green_weight
        ORDER BY form_type, id
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    )
)
INSERT INTO groomed.checks(code, "table", id)
SELECT 'LADUP', 'landings', id
FROM   dup_row_form_types
WHERE  row_number > 1
AND    first_form_type = 'CEL'
AND    all_cel_clr;

UPDATE groomed.landings
SET    checks=array_append(checks,'LADUP'), dropped=TRUE
FROM   groomed.checks u
WHERE  u.code = 'LADUP'
AND    groomed.landings.id = u.id;

$$ LANGUAGE SQL VOLATILE;

SELECT LADUP();

Effects of grooming

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