with cases as (
select
substring(case_number, 0, 13) as case_number,
ctype as case_type,
cast(strftime('%Y', file_date) as int) as year
from
[chips].c_master
where ctype = ${case_type}
union all
select
case_number,
ctype,
cast(strftime('%Y', file_date) as int) as year
from
[chips].r_master
where ctype = ${case_type}
union all
select
substr(r_case_number, 1, 6) || '0' || substr(r_case_number, 7, 5) as case_number,
case_type,
cast(strftime('%Y', date_filed) as int) as year
from
[cats].r_case
where case_type = ${case_type}
union all
select
substr(c_case_number, 1, 6) || '0' || substr(c_case_number, 7, 5) as case_number,
case_type,
cast(strftime('%Y', date_filed) as int) as year
from
[cats].c_case
where case_type = ${case_type}
union all
select
case_number,
case_type,
cast(strftime('%Y', date_filed) as int) as year
from
[nlrb].filing
where case_type = ${case_type}
)
select
year,
case_type,
count(distinct case_number) as count
from
cases
where
year >= 1984
and year < cast(strftime('%Y', 'now') as int)
group by
year
