Jump to content

Combining Select Queries


yeppy12
 Share

Recommended Posts

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'

 

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

  • Thanks 1
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...