inner join help - 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

inner join help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-17 : 06:06:52

Hey guys

I am a little stuck on how to work something out, and i am hoping you will be able to help me

I have built my first query which identifies the following mcc codes ( as seen below)
---three months --

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
Dim_Outlet.External_Account_No,
Dim_Outlet.Legal_Name,
Dim_Outlet.DBA_Name,
Dim_Outlet.Account_Status,
Dim_Outlet.MCC_Code,
FROM Dim_Outlet INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and (dbo.Fact_Financial_History.hst_date_processed >= @date)
group by Dim_Outlet.External_Account_No, Dim_Outlet.Legal_Name,Dim_Outlet.DBA_Name, Dim_Outlet.Account_Status, Dim_Outlet.MCC_Code

My second query, then provides me a sales amount for those mcc codes in the last 12 months. ( as seen below)
---------Rolling 12 ----

Declare @Rolling12tempFROM varchar(10)
Declare @Rolling12tempTO varchar(10)
set @Rolling12tempFROM = (select dateadd(MM,-11,max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])
set @Rolling12tempTO = (select (max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])

SELECT
Dim_Outlet.External_Account_No,
SUM (dbo.Fact_Financial_History.hst_sales_amt) as Sales_Amount12
FROM Dim_Outlet INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and ( hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
group by Dim_Outlet.External_Account_No

I want the column Sales_Amount12 joined onto the first query via the Dim_Outlet.External_Account_No

Can any one help me with this ?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 06:31:41

Try the following:

SELECT col1,.......
INTO #Result1
FROM...........

SELECT col1,.....
INTO #Result2
FROM........

SELECT <required columns>
FROM #result1 JOIN #result2 ON <conition>

DROP TABLE #result1
DROP TABLE #result2

--
Chandu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-17 : 06:58:21

hi bandi

I am getting the following error msg
Msg 4145, Level 15, State 1, Line 46
An expression of non-boolean type specified in a context where a condition is expected, near 'External_Account_No'.

my query is as follows

---three months --

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
Dim_Outlet.External_Account_No,
Dim_Outlet.Legal_Name,
Dim_Outlet.DBA_Name,
Dim_Outlet.Account_Status,
Dim_Outlet.MCC_Code
into #Quarter
FROM Dim_Outlet INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and (dbo.Fact_Financial_History.hst_date_processed >= @date)
group by Dim_Outlet.External_Account_No, Dim_Outlet.Legal_Name,Dim_Outlet.DBA_Name, Dim_Outlet.Account_Status, Dim_Outlet.MCC_Code

---------Rolling 12 ----

Declare @Rolling12tempFROM varchar(10)
Declare @Rolling12tempTO varchar(10)
set @Rolling12tempFROM = (select dateadd(MM,-11,max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])
set @Rolling12tempTO = (select (max(Period))
from [FDMS].[dbo].[Fact_ProcessingCost])

SELECT
Dim_Outlet.External_Account_No,
SUM (dbo.Fact_Financial_History.hst_sales_amt) as Sales_Amount12
into #Rolling12
FROM Dim_Outlet INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and ( hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
group by Dim_Outlet.External_Account_No

select * from #Quarter join #Rolling12 on #Rolling12.External_Account_No

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 07:20:03

select *
from #Quarter
join #Rolling12
on #Rolling12.External_Account_No = #Quarter.External_Account_No

--
Chandu

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-17 : 07:39:32

HI Bandi

I have one more question,

I have added on another part to my query “rolling 18” which i would like the column “Sales_Amount18” joined onto my quarter table. How can i join my sales_amount12 & sales_amount18 to my quarter table via the external_accoun_no?

---three months --

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

SELECT
Dim_Outlet.External_Account_No,
Dim_Outlet.Legal_Name,
Dim_Outlet.DBA_Name,
Dim_Outlet.Account_Status,
Dim_Outlet.MCC_Code
into #Quarter
FROM Dim_Outlet INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and (dbo.Fact_Financial_History.hst_date_processed >= @date)
group by Dim_Outlet.External_Account_No, Dim_Outlet.Legal_Name,Dim_Outlet.DBA_Name, Dim_Outlet.Account_Status, Dim_Outlet.MCC_Code

---------Rolling 12 ----

Declare @Rolling12tempFROM varchar(10)
Declare @Rolling12tempTO varchar(10)
set @Rolling12tempFROM = (select dateadd(MM,-11,max(hst_date_processed))
from dbo.Fact_Financial_History)
set @Rolling12tempTO = (select (max(hst_date_processed))
from dbo.Fact_Financial_History)

SELECT
Dim_Outlet.External_Account_No,
SUM (dbo.Fact_Financial_History.hst_sales_amt) as Sales_Amount12
into #Rolling12
FROM Dim_Outlet INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and ( hst_date_processed BETWEEN @Rolling12tempFROM and @Rolling12tempTO)
group by Dim_Outlet.External_Account_No

---------Rolling 18 ----

Declare @Rolling18tempFROM varchar(10)
Declare @Rolling18tempTO varchar(10)
set @Rolling18tempFROM = (select dateadd(MM,-17,max(hst_date_processed))
from dbo.Fact_Financial_History)
set @Rolling18tempTO = (select (max(hst_date_processed))
from dbo.Fact_Financial_History)

SELECT
Dim_Outlet.External_Account_No,
SUM (dbo.Fact_Financial_History.hst_sales_amt) as Sales_Amount18
into #Rolling18
FROM Dim_Outlet INNER JOIN
Fact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnum
Where MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and ( hst_date_processed BETWEEN @Rolling18tempFROM and @Rolling18tempTO)
group by Dim_Outlet.External_Account_No

select * from #Quarter
join #Rolling12 on #Rolling12.External_Account_No = #Quarter.External_Account_No
join #Rolling18 on #Rolling18.External_Account_No = #Quarter.External_Account_No

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-17 : 07:48:25

You did in correct way..........

Your query will work fine to add one more Rolling18 table via External_Account_No

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources