SET SCHEMA JEMSHDP21;

WITH
ALL_CASES (CASE_NUMBER) AS (SELECT DISTINCT BBCRCD FROM CCECOCP WHERE BBKNDT=0 OR (BBKNDT>=1110101 AND BBKNDT<=1110131)),
ALL_CASES_W_FILINGS (CASE_NUMBER, FILING_CODE, WARRANT_TYPE, FILING_DATE, COMPLETION_DATE) AS
(SELECT CASE_NUMBER, A3CNCD, AWSTCE, A3AQDT, A3EHDT FROM ALL_CASES 
LEFT JOIN CCECDCP ON CASE_NUMBER=A3CRCD
LEFT JOIN CCADCRP ON AWCNCD=A3CNCD
WHERE A3AQDT<1110101 AND AWSTCE IN ('ACW', 'CAP', 'BFA', 'AJW', 'JCW', 'WCW') AND 
(A3EHDT=0 OR (A3EHDT>=1110101 AND A3EHDT<=1110131)))
SELECT 'ROW_1A', COUNT(*) FROM ALL_CASES AS ROW_1A
UNION 
SELECT 'ROW_1B', COUNT(DISTINCT CASE_NUMBER) FROM ALL_CASES_W_FILINGS AS ROW_1B;

SET SCHEMA JEMSHDP41;
WITH
ROW_2_CASES (CASE_NUMBER) AS (SELECT DISTINCT AXCRCD FROM CCDCSRP
WHERE AXADDT>=1110101 AND AXADDT<=1110131 AND AXDUST IN ('C', 'T'))
SELECT COUNT(*) FROM ROW_2_CASES;