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-18 : 07:32:40



Hey guys

I need some more help
i have built two separate queries, however i would like them to be joined together

the common link between both queries is the FDMSACCOUNTno
Form the second query i would like the following columns brought over (chargeback_amount, & chargeback_count)and added to the following query


this is my first query

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


















Query2


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

SELECT
Fact_Fee_History.FDMSAccountNo,
--Fact_Financial_History.hst_merchnum,
Fact_Fee_History.Fee_Sequence_Number,
sum (Fact_Fee_History.Retail_amount) as chargeback_amount,
count (Fact_Fee_History.Fee_Sequence_Number)as chargeback_count
FROM Dim_Outlet INNER JOIN
Fact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNo
where Fee_Sequence_Number = '236'
and (Month_end_date >= @date)
and Dim_Outlet.MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
group by
Fact_Fee_History.FDMSAccountNo,
--hst_merchnum,
Fee_Sequence_Number
order by chargeback_amount desc

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 02:58:28
Hi Masond,

I would like to clarify my doubts......

1) Why you are using following @date for each query..
Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)

You can do this filteration in your actual query itself....

WHERE dbo.Fact_Financial_History.hst_date_processed >= (select dateadd(MM,-2,max(hst_date_processed)) from FDMS.dbo.Fact_Financial_History)



2) One more thing is Try to use Aliases for tables while joining

FROM Dim_Outlet do
INNER JOIN Fact_Financial_History ffh ON Dim_Outletdo.FDMSAccountNo_First9 = Fact_Financial_Historyffh.hst_merchnum

Then it is easy to understand code(because it minimizes code length)



--
Chandu
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-19 : 03:13:26
Hi,


SELECT A.*, B.Fee_Sequence_Number, B.chargeback_amount, B.chargeback_count
INTO #Quarter
FROM (SELECT DO.FDMSAccountNo,DO.External_Account_No,DO.Legal_Name,DO.DBA_Name, DO.Account_Status, DO.MCC_Code
FROM Dim_Outlet DO
INNER JOIN Fact_Financial_History ffh ON DO.FDMSAccountNo_First9 = ffh.hst_merchnum
WHERE MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
and ffh.hst_date_processed >= (select dateadd(MM,-2,max(hst_date_processed)) from FDMS.dbo.Fact_Financial_History)
GROUP BY DO.FDMSAccountNo,DO.External_Account_No, DO.Legal_Name,DO.DBA_Name, DO.Account_Status, DO.MCC_Code) A
INNER JOIN
(SELECT fh.FDMSAccountNo,fh.Fee_Sequence_Number,sum (fh.Retail_amount) as chargeback_amount,count (fh.Fee_Sequence_Number)as chargeback_count
FROM Dim_Outlet do
INNER JOIN Fact_Fee_History fh ON do.FDMSAccountNo = fh.FDMSAccountNo
WHERE Fee_Sequence_Number = '236'
and Month_end_date >= (select dateadd(MM,-2,max(hst_date_processed)) from FDMS.dbo.Fact_Financial_History)
and do.MCC_Code in ('4814','4816','5967','7273','7841','7995','9754','9399','9754','5122','5912','5993')
GROUP BY fh.FDMSAccountNo, Fee_Sequence_Number) B
ON A.FDMSAccountNo = B.FDMSAccountNo


Here i joined First SELECT query (i.e. #Quarter part) and Your Query2

Then you combine this result set with #Rolling12 and #Rolling18






--
Chandu
Go to Top of Page
   

- Advertisement -