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 - 2013-03-14 : 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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-03-14 : 13:00:15

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources