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

Join Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-22 : 08:25:37

Aim- Append #ORolling results onto #RM , if there is a same ParentID, in #ORolling and Rm, Then sum the Sales from #rolling, with the sales in #RM

--Omnipay Date Declaration --
Declare @R12Omni datetime
set @R12Omni = (select dateadd(MM,-11,max(Period))
from [FDMS].[dbo].[Fact_Omnipay_Profitability])
select @R12Omni

--Omnipay Rolling 12 Sales --
SELECT
ParentID,
Sum([Tot_Purch_Amt]) as 'Sales'
into #ORolling
FROM Fact_Omnipay_Profitability
INNER JOIN Dim_Outlet ON Fact_Omnipay_Profitability.FDMSAccountNo = Dim_Outlet.FDMSAccountNo
where Period >= @R12Omni
group by Dim_Outlet.ParentID

-- Rm --
SELECT
Dim_Outlet.ParentID,
Dim_Outlet.Parent_Name,
Account_status,
RM_Account,
sum(Fact_Financial_History_2.hst_sales_amt_R12) as Sales
into #RM
FROM Fact_Financial_History_2
INNER JOIN Dim_Outlet ON Fact_Financial_History_2.hst_merchnum = Dim_Outlet.FDMSAccountNo_First9
and Agent_Chain_No not in ('878970059886', '878970013883')
group by
parent_name,
parentid,
account_status,
RM_Account
having SUM (hst_sales_amt_R12) > 8000000
Order by sales asc

Looking forward to your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 08:43:51

[code]
SELECT r.ParentID,r.Sales + COALESCE(rm.Sales,0) AS TotalSales
FROM #ORolling r
LEFT JOIN #RM rm
ON rm.ParentID = r.ParentID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-22 : 09:32:25

HI

Thank you for your response, there is a vital piece of information i forgot to add?. Currently the query you built adds the financials from #ORolling on to #RM.

Although this is correct and exactly what i need, however when there isnt a match in both tables i need the parent iD produdce with the sales

For instance there can be a parentid, in #Rm which isnt in #orlling, and i need this parentId to be included in the final result set

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-22 : 10:06:29

[code]
SELECT COALESCE(r.ParentID,rm.ParentID) AS ParentID,COALESCE(r.Sales,0) + COALESCE(rm.Sales,0) AS TotalSales
FROM #ORolling r
FULL JOIN #RM rm
ON rm.ParentID = r.ParentID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources