So here is what I have so far...thoughts?
select
acct_num = ser.account_number,
customer_type = cac.member_role,
full_name = cac.full_name,
acc.charge_off_date,
external_status = acc.external_status_code_name,
coff_reason = acc.charge_off_reason_code,
crd.date_status_chg,
cac.deceased_code,
crd.upc_14,
curr_bal = ser.current_balance,
cac.cii_code,
sys_num = spa.system_bank_identifier
into #first_filters
from fact.fact_cc_servicing ser
join dim.dim_cc_account acc on ser.account_number = acc.account_number and acc.end_date = 99991231
join dim.dim_cc_account_customer cac on ser.account_number = substring(cac.full_account, 13, 16) and cac.end_date = 99991231
join dim.dim_cc_cardholder crd on ser.account_number = crd.account_number and crd.end_date = 99991231
join dim.dim_cc_sys_prin_agent spa on ser.dim_cc_sys_prin_agent_key = spa.dim_cc_sys_prin_agent_key
where ser.end_date = 99991231
and acc.external_status_code_name = 'charged off'
and acc.charge_off_date between 20180101 and 20181231
and ser.current_balance >= 600
and cac.member_role = '01'
order by ser.account_number
insert into #first_filters (
acct_num,
customer_type,
full_name,
acc.charge_off_date,
external_status,
coff_reason,
crd.date_status_chg,
cac.deceased_code,
crd.upc_14,
curr_bal,
cac.cii_code,
sys_num)
select *
from #first_filters
where (sys_num = '3616'
and isnull(cii_code, '$') in ('E', 'F', 'G', 'H'))
or coff_reason = '68'
select * from #first_filters