SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Looking to merge 2 queries into one (not a union)
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mxfrail
Yak Posting Veteran

84 Posts

Posted - 10/11/2012 :  10:53:35  Show Profile  Reply with Quote
Hi,

I have two queries with slightly different where clauses that I would like to combine into 1 query.

Query 1 -

select	count(distinct CASE WHEN a11.P1_CALLS>0 THEN a11.PRSBR_CID ELSE NULL END)  WJXBFS1,
	sum(a11.P1_CALLS)  WJXBFS2,
	sum(a11.SMPL)  WJXBFS3,
	count(distinct CASE WHEN a11.CALLS>0 THEN a11.PRSBR_CID ELSE NULL END)  WJXBFS4,
	count(distinct CASE WHEN a11.SMPL>0 THEN a11.PRSBR_CID ELSE NULL END)  WJXBFS5
from	MSTRATDM.FCT_PRSB_ACT	a11
	join	MSTRATDM.DIM_ACTIVE_PRSBR	a12
	  on 	(a11.PRSBR_PIT_SID = a12.PRSBR_PIT_SID)
	join	MSTRATDM.DRV_DATA_RLG_WK	a13
	  on 	(a11.CALN_WK_STTC_SID = a13.CALN_WK_STTC_SID)
	join	MSTRATDM.DIM_PROD_DM_GRP	a14
	  on 	(a11.PROD_ABBR = a14.CORP_PROD_GRP_ABBR and 
	a11.PROD_DM_GRP_STTC_SID = a14.PROD_DM_GRP_STTC_SID)
where	(a14.PROD_MKT_DM_STTC_SID in (601500)
 and a13.DATA_RLG_WK_NUM = 0
 and a14.CORP_PROD_GRP_NM in ('test1')
 and a12.BRO_SLS_ALIGN_LVL7_NUM not like 'D9%') 


Query 2 -

 select	sum((a11.TCU * a11.PDRP_FCTR))  WJXBFS6,
	sum((a11.TRX * a11.PDRP_FCTR))  WJXBFS7
from	MSTRATDM.FCT_PRSB_PGP_W	a11
	join	MSTRATDM.DIM_ACTIVE_PRSBR	a12
	  on 	(a11.PRSBR_PIT_SID = a12.PRSBR_PIT_SID)
	join	MSTRATDM.DRV_DATA_RLG_WK	a13
	  on 	(a11.DATA_RLG_WK_NUM = a13.DATA_RLG_WK_NUM)
	join	MSTRATDM.DIM_PROD_DM_GRP	a14
	  on 	(a11.PROD_DM_GRP_STTC_SID = a14.PROD_DM_GRP_STTC_SID and 
	a11.TC_ABBR = a14.TC_ABBR)
where	(a14.PROD_MKT_DM_STTC_SID in (601500)
 and a11.EXCLSN_FCTR = 1
 and a13.DATA_RLG_WK_NUM = 0
 and a14.CORP_PROD_GRP_NM in ('test1')
 and a12.BRO_SLS_ALIGN_LVL7_NUM not like 'D9%') 


Could anyone help with this?

lazerath
Constraint Violating Yak Guru

USA
319 Posts

Posted - 10/11/2012 :  11:59:16  Show Profile  Reply with Quote
Aside from the EXCLSN_FACTR = 1, the table aliased "A11" is different in each query. Additionally, the output is different. I'm inferring a bit from your question, but I think you want both sets of output displayed in the same row. If that's the case, here is something that would help you:


;WITH CTE1
AS
(
select	count(distinct CASE WHEN a11.P1_CALLS>0 THEN a11.PRSBR_CID ELSE NULL END)  WJXBFS1,
	sum(a11.P1_CALLS)  WJXBFS2,
	sum(a11.SMPL)  WJXBFS3,
	count(distinct CASE WHEN a11.CALLS>0 THEN a11.PRSBR_CID ELSE NULL END)  WJXBFS4,
	count(distinct CASE WHEN a11.SMPL>0 THEN a11.PRSBR_CID ELSE NULL END)  WJXBFS5
	
from	MSTRATDM.FCT_PRSB_ACT	a11
	join	MSTRATDM.DIM_ACTIVE_PRSBR	a12
	  on 	(a11.PRSBR_PIT_SID = a12.PRSBR_PIT_SID)
	join	MSTRATDM.DRV_DATA_RLG_WK	a13
	  on 	(a11.CALN_WK_STTC_SID = a13.CALN_WK_STTC_SID)
	join	MSTRATDM.DIM_PROD_DM_GRP	a14
	  on 	(a11.PROD_ABBR = a14.CORP_PROD_GRP_ABBR and 
	a11.PROD_DM_GRP_STTC_SID = a14.PROD_DM_GRP_STTC_SID)
where	(a14.PROD_MKT_DM_STTC_SID in (601500)
 and a13.DATA_RLG_WK_NUM = 0
 and a14.CORP_PROD_GRP_NM in ('test1')
 and a12.BRO_SLS_ALIGN_LVL7_NUM not like 'D9%') 
 ) , CTE2
 AS
 (
 select	
	sum((a11.TCU * a11.PDRP_FCTR))  WJXBFS6,
	sum((a11.TRX * a11.PDRP_FCTR))  WJXBFS7
from	MSTRATDM.FCT_PRSB_PGP_W	a11
	join	MSTRATDM.DIM_ACTIVE_PRSBR	a12
	  on 	(a11.PRSBR_PIT_SID = a12.PRSBR_PIT_SID)
	join	MSTRATDM.DRV_DATA_RLG_WK	a13
	  on 	(a11.DATA_RLG_WK_NUM = a13.DATA_RLG_WK_NUM)
	join	MSTRATDM.DIM_PROD_DM_GRP	a14
	  on 	(a11.PROD_DM_GRP_STTC_SID = a14.PROD_DM_GRP_STTC_SID and 
	a11.TC_ABBR = a14.TC_ABBR)
where	(a14.PROD_MKT_DM_STTC_SID in (601500)
 and a11.EXCLSN_FCTR = 1
 and a13.DATA_RLG_WK_NUM = 0
 and a14.CORP_PROD_GRP_NM in ('test1')
 and a12.BRO_SLS_ALIGN_LVL7_NUM not like 'D9%') 
)
SELECT * FROM CTE1 CROSS JOIN CTE2


This could be more elegant / efficient by abstracting the table joins and where criteria from A12, A13, A14 to a new CTE (say CTE0) and then using that as a basis for CTE1 & CTE2.

In the future, try to follow this advice when posting questions:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Edited by - lazerath on 10/11/2012 13:11:07
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
319 Posts

Posted - 10/11/2012 :  13:15:10  Show Profile  Reply with Quote
Ok, Ignore the part about creating a CTE0 with A12, A13 & A14. I didn't pay close enough attention. This is obviously a data mart or data warehouse with a star or snowflake schema, and since your A11 tables are both separate FACT tables, you'll need to filter and aggregate them separately as shown.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000