filing_ids and amendments missing cover sheets

filing_ids_and_amend_missing_cvr.sql

SELECT a."FILING_ID", a."AMEND_ID", COUNT(*)
FROM "SMRY_CD" a
LEFT JOIN "CVR_CAMPAIGN_DISCLOSURE_CD" b
ON a."FILING_ID" = b."FILING_ID"
AND a."AMEND_ID" = b."AMEND_ID"
WHERE b."FILING_ID" IS NULL
AND a."FORM_TYPE" IN (
        -- get the possible SMRY_CD form_type values for the F460 filings in CVR_CAMPAIGN_DISCLOSURE_CD
        SELECT DISTINCT a."FORM_TYPE"
        FROM "SMRY_CD" a
        JOIN "CVR_CAMPAIGN_DISCLOSURE_CD" b
        ON a."FILING_ID" = b."FILING_ID"
        AND a."AMEND_ID" = b."AMEND_ID"
        WHERE b."FORM_TYPE" = 'F460'
)
GROUP BY 1, 2
ORDER BY 1, 2;

FILING_ID  AMEND_ID  count  
---------  --------  -----  
591205     0         60     
591313     0         60     
591426     0         60     
591440     0         60     
871773     1         45     
1172839    1         38     
1346256    1         38     
1346489    1         38     
1852507    2         47