Combining Select Queries

[yeppy12]

I am a newbie with SQL.  I wrote the code below, and I am trying to make it more efficient.  As of now, I run the first select statement, copy/paste it into excel, run the second part, and then copy/paste it into excel.

Is there a way to combine these queries into one?  I was unsure of how to do so.  I currently run the select statements separately because I want the results from the first select statement AND the results from the second statement.  The second select statement just uses the temp table #first_filters created in the first select statement to get records meeting the criteria.

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
        join dim.dim_cc_account_customer cac on ser.account_number = substring(cac.full_account, 13, 16)
        join dim.dim_cc_cardholder crd on ser.account_number = crd.account_number
	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 --gives the current record from the fact table
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

select *

from #first_filters

where (sys_num = '3616'
and isnull(cii_code, '$') in ('E', 'F', 'G', 'H'))
or coff_reason = '68'

 

[niche]

Sounds like you want something like this:

https://stackoverflow.com/questions/14907863/insert-data-from-one-table-to-another-in-mysql

[yeppy12]

So I read that article on insert into, but I am still confused as to how I can combine the where clauses in my query.  The examples in that article do not have where clauses.

 

[justsomeguy]

You don't need to make a separate query.  It's not going to be any more efficient, but you should be able to this:

select * from (
	  select * from ...
	) AS tmp
	WHERE...

[niche]

https://www.w3schools.com/sql/sql_insert_into_select.asp

 

[yeppy12]

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

 

[niche]

My understanding is # is for comments

[justsomeguy]

You're still using a temporary table, I don't think there's any reason to, especially if this is a multi-user application.  If it's single-user then you could benefit from creating a permanent table and giving it the correct indexes, otherwise there's no point to use a temporary table.

	select *
	from (
	 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
	from fact.fact_cc_servicing ser 
    join dim.dim_cc_account acc on ser.account_number = acc.account_number
        join dim.dim_cc_account_customer cac on ser.account_number = substring(cac.full_account, 13, 16)
        join dim.dim_cc_cardholder crd on ser.account_number = crd.account_number
    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 --gives the current record from the fact table
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'
	) AS tmp
	where (sys_num = '3616'
and isnull(cii_code, '$') in ('E', 'F', 'G', 'H'))
or coff_reason = '68'

If you want to order the results, order the outer query, there's no reason to order the inner one.

[yeppy12]

Ahh, okay.  Thanks, justsomeguy.  I will try that out.