|
masond
Constraint Violating Yak Guru
251 Posts |
 Posted - 03/14/2013 : 12:52:14
|
Hey guys
Sorry for posting such silly question but i am really struggling to inner join my query
There is approx 10 temp tables which i have created and all i want to do is join all the tables to gether by the ParentID
This is my query
-- financials--
SELECT
Dim_Outlet.ParentID,
sum(Fact_Financial_History_2.Net_Sales_R12) as 'Financial R12',
sum(Fact_Financial_History_2.Net_Intg_R12)as 'Interchange R12',
sum(Fact_Financial_History_2.Scheme_Fees_R12)as 'Scheme R12',
sum(Fact_Financial_History_2.Funding_Amt_R12)as 'Funding R12'
into #MsipFinancials
FROM Fact_Financial_History_2
INNER JOIN Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
group by ParentID
-- Processing--
SELECT
Dim_Outlet.ParentID,
SUM(Fact_ProcessingCost_2.Value_R12) As 'Processing R12'
into #MsipProcessing
FROM Dim_Outlet
INNER JOIN Fact_ProcessingCost_2 ON Dim_Outlet.FDMSAccountNo = Fact_ProcessingCost_2.FDMSAccountNo
group by ParentID
-- MSC --
SELECT
Dim_Outlet.ParentID,
sum(Fact_Fee_History_2.Retail_amount_R12) as 'MSC R12'
into #MsipMSC
FROM Dim_Outlet INNER JOIN Fact_Fee_History_2 ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History_2.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History_2.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where SalesMI_Group1 = 'MSC' and Fee_Code <> 'M01'
group by ParentID
-- M01--
SELECT
Dim_Outlet.ParentID,
sum(Fact_Fee_History_2.Retail_amount_R12) as 'M01 R12'
into #MsipM01
FROM Dim_Outlet INNER JOIN Fact_Fee_History_2 ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History_2.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History_2.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where Fee_Code = 'M01'
group by ParentID
-- M2E Refund--
SELECT
Dim_Outlet.ParentID,
sum(Fact_Fee_History_2.Retail_amount_R12) as 'M2E R12'
into #MsipM2E
FROM Dim_Outlet INNER JOIN Fact_Fee_History_2 ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History_2.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History_2.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where Fee_Code = 'M2E'
group by ParentID
--- Pci r12 --
SELECT
Dim_Outlet.ParentID,
sum(Fact_Fee_History_2.Retail_amount_R12) as 'Pci R12'
into #MsipPci
FROM Dim_Outlet INNER JOIN Fact_Fee_History_2 ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History_2.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History_2.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where fee_Code IN ('42B','42C','42D','42E')
group by ParentID
-- Other Income --
SELECT
Dim_Outlet.ParentID,
sum(Fact_Fee_History_2.Retail_amount_R12) as 'Other Income R12'
into #MsipOtherincome
FROM Dim_Outlet INNER JOIN Fact_Fee_History_2 ON Dim_Outlet.FDMSAccountNo = Fact_Fee_History_2.FDMSAccountNo
INNER JOIN Dim_Fee_Codes ON Fact_Fee_History_2.Fee_Sequence_Number = Dim_Fee_Codes.Fee_Code
where salesMI_Group1 IN ('other', 'N/A')
and repeat_msc = 'Y'
group by ParentID
-- sales by region --
SELECT
Dim_Outlet.ParentID,
sum(case when Region = 'UK' Then (Net_Sales_R12) else 0 end )as UK,
sum(case when Region = 'EU' Then (Net_Sales_R12) else 0 end) as EU,
sum(case when Region = 'NT' Then (Net_Sales_R12) else 0 end )as NT,
isnull(sum(case when Region = 'UK' Then (Net_Sales_R12) else 0 end),0)/ nullif (SUM(Net_Sales_R12),0) as [UK%],
isnull(sum(case when Region = 'EU' Then (Net_Sales_R12) else 0 end),0)/ nullif (SUM(Net_Sales_R12),0) as [EU%],
isnull(sum(case when Region = 'NT' Then (Net_Sales_R12) else 0 end),0)/ nullif (SUM(Net_Sales_R12),0) as [NT%]
into #MsipSalesRegion
FROM Dim_Outlet
INNER JOIN Fact_Financial_History_2 ON Dim_Outlet.FDMSAccountNo_First9 = Fact_Financial_History_2.hst_merchnum
INNER JOIN Dim_Interchange_Tier_2 ON Fact_Financial_History_2.Plan_Key = Dim_Interchange_Tier_2.Plan_Code
group by ParentID
-- Di-
-- (Msc without m01 adjustments)- Interchange
-- Dia --
--Msc without m01 adjustments)- Interchange - scheme --
--Repeatable Contribution --
-- Dia (Msc without m01 adjustments) + Other income + Funding - Processing --
------Omni Pay figures -----
---- Date delcaration ------
Declare @R12from datetime
set @R12from = (select dateadd(MM,-11,max(Period))
from [FDMS].[dbo].[Fact_Omnipay_Profitability])
select @R12from
-- Ic plus Checker --
-- IC Idicator logic -- There are possiblities that some months merchants will be nonIC and then change to IC+
--Have done a count which looks at the month, and whatever the majority Indiciator is, the check will default to --
;
with cte as (
SELECT
Dim_Outlet.ParentID,
period,
sum([Merch_Purch_Fees]) as Purch_fees,
case when merch_purch_fees < 0 then 'Non IC+'end as 'Non_IC',
case when merch_purch_fees > = 0 then 'IC+' end as 'IC Indicator'
FROM Fact_Omnipay_Profitability
INNER JOIN Dim_Outlet ON Fact_Omnipay_Profitability.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
where period >= @R12from
group by Dim_Outlet.ParentID,Merch_Purch_Fees, Period)
Select
ParentID,
Period,
case when count(Non_IC) > count([IC Indicator])then 'NonIC'
When count([IC Indicator]) > count(Non_IC)then 'IC++' else 'Check' end as 'Check'
Into #OmnipayIC
from cte
group by ParentID,Period
-- Onnipay MSC--
SELECT
m.Period,
o.ParentID,
i.[Check],
sum(Intchg_Assess) as OInterR12 ,
-SUM(CASE WHEN merch_purch_fees < 0 THEN [Merch_Purch_Fees]
WHEN merch_purch_fees > = 0 THEN [Merch_Purch_Fees] + [Per_Tran_Fees] END) AS MSC,
case
when [check] = 'ic++' then sum(Intchg_Assess)+ -SUM(CASE WHEN merch_purch_fees < 0 THEN [Merch_Purch_Fees]
WHEN merch_purch_fees > = 0 THEN [Merch_Purch_Fees] + [Per_Tran_Fees] end)
when [check] = 'NonIC' then sum(Intchg_Assess)+ -SUM(CASE WHEN merch_purch_fees < 0 THEN [Merch_Purch_Fees]
WHEN merch_purch_fees > = 0 THEN [Merch_Purch_Fees] + [Per_Tran_Fees] end) end as CorrectMsc
Into #OmnipayMSC
FROM [FDMS].[dbo].[Fact_Omnipay_Profitability]m
INNER JOIN Dim_Outlet o ON m.FDMSAccountNo = o.FDMSAccountNo
inner join #OmnipayIC i on o.ParentID = i.ParentID
and i.Period = m.Period
where m.Period >= @R12from
GROUP BY o.ParentID, m.Period,i.[Check]
-- Onnipay financials--
SELECT
Dim_Outlet.ParentID,
Sum([Tot_Purch_Amt])-sum(-[Refund_Amt]) as [OR12],
sum([Intchg_Assess]) as [OInter R12]
into #OmnipayFinancials
FROM Fact_Omnipay_Profitability
INNER JOIN Dim_Outlet ON Fact_Omnipay_Profitability.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
where Period >= @R12from
group by Dim_Outlet.ParentID
|
|
inner join help
