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 FDMSACCOUNTnoForm the second query i would like the following columns brought over (chargeback_amount, & chargeback_count)and added to the following querythis 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_Codeinto #QuarterFROM Dim_Outlet INNER JOINFact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnumWhere 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_Amount12into #Rolling12FROM Dim_Outlet INNER JOINFact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnumWhere 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_Amount18into #Rolling18FROM Dim_Outlet INNER JOINFact_Financial_History ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History.hst_merchnumWhere 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_Noselect * from #Quarter join #Rolling12 on #Rolling12.External_Account_No = #Quarter.External_Account_Nojoin #Rolling18 on #Rolling18.External_Account_No = #Quarter.External_Account_No Query2Declare @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_countFROM Dim_Outlet INNER JOINFact_Fee_History ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History.FDMSAccountNowhere 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_Numberorder 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 joiningFROM Dim_Outlet doINNER JOIN Fact_Financial_History ffh ON Dim_Outletdo.FDMSAccountNo_First9 = Fact_Financial_Historyffh.hst_merchnumThen it is easy to understand code(because it minimizes code length)--Chandu |
|
|
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_countINTO #QuarterFROM (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) AINNER 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) BON A.FDMSAccountNo = B.FDMSAccountNo Here i joined First SELECT query (i.e. #Quarter part) and Your Query2Then you combine this result set with #Rolling12 and #Rolling18--Chandu |
|
|
|
|
|