Jump to content
yeppy12

Combining Select Queries

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'

 

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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...

Share this post


Link to post
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

 

Share this post


Link to post
Share on other sites

My understanding is # is for comments

Share this post


Link to post
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

Share this post


Link to post
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

×