SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 inner join help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

masond
Constraint Violating Yak Guru

447 Posts

Posted - 03/14/2013 :  12:52:14  Show Profile  Reply with Quote
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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 03/14/2013 :  13:00:15  Show Profile  Reply with Quote
is it as simple as

from #MsipFinancials
inner join #MsipProcessing on #MsipFinancials.ParentID = #MsipProcessing.ParentID
inner join #MsipMSC on #MsipFinancials.ParentID = #MsipMSC.ParentID
inner join #MsipM01 on #MsipFinancials.ParentID = #MsipM01.ParentID
inner join #MsipM2E on #MsipFinancials.ParentID = #MsipM2E.ParentID
inner join #MsipPci on #MsipFinancials.ParentID = #MsipPci.ParentID
inner join #MsipOtherincome on #MsipFinancials.ParentID = #MsipOtherincome.ParentID
inner join #MsipSalesRegion on #MsipFinancials.ParentID = #MsipSalesRegion.ParentID
inner join #OmnipayIC on #MsipFinancials.ParentID = #OmnipayIC.ParentID
inner join #OmnipayMSC on #MsipFinancials.ParentID = #OmnipayMSC.ParentID
inner join #OmnipayFinancials on #MsipFinancials.ParentID = #OmnipayFinancials.ParentID
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000