Looking to merge 2 queries into one (not a union)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Looking to merge 2 queries into one (not a union)

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2012-10-11 : 10:53:35

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

343 Posts

Posted - 2012-10-11 : 11:59:16

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

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-10-11 : 13:15:10

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources