Naming columns - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Naming columns

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-14 : 05:19:45

Hey guys

Sorry for the stupid question, but i hope it can be done

I have two queries, they both have the same logic, however the only difference is the the MCC and what I am calling them

Is there anyway i can join the two queries together? And do unique counts for them?

So for eg the end result layout would be

Total Retail Locations 10
Total Travel & Entertainment Locations 5000

This is my query

SELECT
COUNT(FDMSAccountNo)+ COUNT(Distinct Corp_Chain_No) as [Total Retail Locations]
FROM Dim_Outlet INNER JOIN
Dim_MCC ON Dim_Outlet.MCC_Code = Dim_MCC.MCC
INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
where Dim_MCC.MCC in ( '5013','5021','5045','5094' ,'5172' ,'5310' ,'5311' ,'5411' ,'5422',
'5441','5451', '5462','5499','5531','5533','5541' ,'5542','5712' ,'5734','5944','5983',
'8011','8021','8031','8041' ,'8042' ,'8043','8049','8050','8062' ,'8071','8099','5137',
'5139','5611','5621','5631' ,'5641' ,'5651','5661', '5681', '5691', '5697', '5698', '5947')
and (dbo.Fact_Financial_History.hst_prod_code ='85')
and Account_Status ='16'

This is my other query

SELECT
COUNT(FDMSAccountNo)+ COUNT(Distinct Corp_Chain_No) as [Total Travel & Entertainment Locations]
FROM Dim_Outlet INNER JOIN
Dim_MCC ON Dim_Outlet.MCC_Code = Dim_MCC.MCC
INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
where Dim_MCC.MCC in ( '5811','5812','5813','5814','5945')
and (dbo.Fact_Financial_History.hst_prod_code ='85')
and Account_Status ='16'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-14 : 05:29:33

[code]SELECT
'Total Retail Locations' as Title,
COUNT(FDMSAccountNo)+ COUNT(Distinct Corp_Chain_No) as [value]
FROM Dim_Outlet INNER JOIN
Dim_MCC ON Dim_Outlet.MCC_Code = Dim_MCC.MCC
INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
where Dim_MCC.MCC in ( '5013','5021','5045','5094' ,'5172' ,'5310' ,'5311' ,'5411' ,'5422',
'5441','5451', '5462','5499','5531','5533','5541' ,'5542','5712' ,'5734','5944','5983',
'8011','8021','8031','8041' ,'8042' ,'8043','8049','8050','8062' ,'8071','8099','5137',
'5139','5611','5621','5631' ,'5641' ,'5651','5661', '5681', '5691', '5697', '5698', '5947')
and (dbo.Fact_Financial_History.hst_prod_code ='85')
and Account_Status ='16'

UNION ALL

SELECT
'Total Travel & Entertainment Locations' as Title,
COUNT(FDMSAccountNo)+ COUNT(Distinct Corp_Chain_No) as [value]
FROM Dim_Outlet INNER JOIN
Dim_MCC ON Dim_Outlet.MCC_Code = Dim_MCC.MCC
INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
where Dim_MCC.MCC in ( '5811','5812','5813','5814','5945')
and (dbo.Fact_Financial_History.hst_prod_code ='85')
and Account_Status ='16'
[/code]

Too old to Rock'n'Roll too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:12:51

[code]
SELECT
COUNT(CASE WHEN Dim_MCC.MCC in ( '5013','5021','5045','5094' ,'5172' ,'5310' ,'5311' ,'5411' ,'5422',
'5441','5451', '5462','5499','5531','5533','5541' ,'5542','5712' ,'5734','5944','5983',
'8011','8021','8031','8041' ,'8042' ,'8043','8049','8050','8062' ,'8071','8099','5137',
'5139','5611','5621','5631' ,'5641' ,'5651','5661', '5681', '5691', '5697', '5698', '5947') THEN FDMSAccountNo END)
+
COUNT(Distinct CASE WHEN Dim_MCC.MCC in ( '5013','5021','5045','5094' ,'5172' ,'5310' ,'5311' ,'5411' ,'5422',
'5441','5451', '5462','5499','5531','5533','5541' ,'5542','5712' ,'5734','5944','5983',
'8011','8021','8031','8041' ,'8042' ,'8043','8049','8050','8062' ,'8071','8099','5137',
'5139','5611','5621','5631' ,'5641' ,'5651','5661', '5681', '5691', '5697', '5698', '5947') THEN Corp_Chain_No END) as [Total Retail Locations],
COUNT(CASE WHEN Dim_MCC.MCC in ( '5811','5812','5813','5814','5945') THEN FDMSAccountNo END)
+ COUNT(Distinct CASE WHEN Dim_MCC.MCC in ( '5811','5812','5813','5814','5945') THEN Corp_Chain_No END) as [Total Travel & Entertainment Locations]
FROM Dim_Outlet INNER JOIN
Dim_MCC ON Dim_Outlet.MCC_Code = Dim_MCC.MCC
INNER JOIN Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
where (dbo.Fact_Financial_History.hst_prod_code ='85')
and Account_Status ='16'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources