Joining two tables 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

Joining two tables Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-17 : 11:28:17

Hey Guys

Hope your well,

I have hit a mental block and i am really hoping you will be able to help me

Aim – Do the following calculation below (
SELECT
Dim_Outlet.ParentID,
Dim_Outlet.Parent_Name,
account_status,
sum(Fact_Financial_History_2.hst_sales_amt_R12) as Sales
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')
and Account_Status ='16'
group by
parent_name,
parentid,
account_status
having SUM (hst_sales_amt_R12) > 2999999 and SUM(hst_sales_amt_R12) < 7999999
Order by sales asc

) however exclude any parentsfid where the report_status = “Accepted” Parentsfid can be found in a temp table called #lbg2

#lbg2 query is

--Aim
----- Trying to find highest level of the fdmsaccountno in the [LBG_Accounts] table,
-- although column is called fdmsaccountno, the mids provided are @ different levels
--
SELECT
lbg.[FDMSAccountNo],
Parentsfid,
[Merchant_Name],
[Report_Status],
case
WHEN lbg.[FDMSAccountNo] = substring([MM-CHN-chain],2,12) THEN 'Chain'
WHEN lbg.[FDMSAccountNo] = substring([MM-CHN-CORP],2,12) THEN 'Corporate'
WHEN lbg.[FDMSAccountNo] = substring([MM-CHN-AGENT],2,12) THEN 'Agent' ELSE 'Outlet' END AS [Type]
Into #lbg
FROM [FDMS].[dbo].[LBG_Accounts] lbg
INNER JOIN stg_LMPAB501 ON stg_LMPAB501.FDMSAccountNo = lbg.FDMSAccountNo

--Finding Highest level mid --

---Outlet to chain --
inner join (
select distinct substring([MM-CHN-chain],2,12) as Parentsfid ,
FDMSAccountNo as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-chain],2,12) <> '000000000000'
and FDMSAccountNo <> substring([MM-CHN-CORP],2,12)
and FDMSAccountNo <> substring([MM-CHN-chain],2,12)

-- Outlet to Corp--
union all
select distinct substring([MM-CHN-CORP],2,12) as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000'
and substring([MM-CHN-chain],2,12) = '000000000000'
and FDMSAccountNo <>substring([MM-CHN-CORP],2,12)

-- Outlet to Agent --
union all
select distinct substring([MM-CHN-AGENT],2,12) as Parentsfid
,fdmsaccountno as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) = '000000000000'
and substring([MM-CHN-chain],2,12) = '000000000000'
or FDMSAccountNo = substring([MM-CHN-AGENT],2,12)

-- Chain to Corp--
union all
select distinct substring([MM-CHN-CORP],2,12) as Parentsfid
,substring([MM-CHN-chain],2,12) as child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) <> '000000000000'
and FDMSAccountNo = substring([MM-CHN-chain],2,12)

-- Chain to agent--
union all
select distinct substring([MM-CHN-AGENT],2,12) as Parentsfid
,substring([MM-CHN-chain],2,12) as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-chain],2,12) <> '000000000000'
and substring([MM-CHN-CORP],2,12) = '000000000000'
and FDMSAccountNo = substring([MM-CHN-chain],2,12)

-- Corp to agent --
union all
select Distinct substring([MM-CHN-AGENT],2,12) as Parentsfid
,substring([MM-CHN-CORP],2,12) as Child
FROM [FDMS].[dbo].[stg_LMPAB501]
where substring([MM-CHN-CORP],2,12) = fdmsaccountno)
x
on x.child = [FDMS].[dbo].[stg_LMPAB501].[FDMSAccountNo]

--This logic makes sure that the parentfid is actually the parent of the FDMSaccountno--
select
#lbg.[FDMSAccountNo],
#lbg.Parentsfid,
#lbg.[Merchant_Name],
#lbg.[Report_Status],
case
WHEN Parentsfid = substring([MM-CHN-chain],2,12) THEN 'Chain'
WHEN Parentsfid = substring([MM-CHN-CORP],2,12) THEN 'Corporate'
WHEN Parentsfid = substring([MM-CHN-AGENT],2,12) THEN 'Agent' ELSE 'Outlet' END AS [Type]
into #lgb1
from #lbg
INNER JOIN stg_LMPAB501 ON stg_LMPAB501.FDMSAccountNo = #lbg.fdmsaccountno
order by [TYPE] desc

---Update is updating the query above, where type column = 'Agent', Agent number is being populated
-- in the parentfsfid column, therefore fdmsaccountno needs to be populated in Parentsfid

--Update ---
update #lgb1
set Parentsfid = FDMSAccountNo
where [TYPE] = 'Agent'
drop table #lbg

-- Final check is making sure that the update has sucessfully worked, and that the parentsfid, is the highest level--
select
#lgb1.FDMSAccountNo,
#lgb1.Parentsfid,
#lgb1.Merchant_Name,
#lgb1.Report_Status,
case
WHEN Parentsfid = substring([MM-CHN-chain],2,12) THEN 'Chain'
WHEN Parentsfid = substring([MM-CHN-CORP],2,12) THEN 'Corporate'
WHEN Parentsfid = substring([MM-CHN-AGENT],2,12) THEN 'Agent' ELSE 'Outlet' END AS [Type]
into #lgb2
from #lgb1
INNER JOIN stg_LMPAB501 ON stg_LMPAB501.FDMSAccountNo = #lgb1.FDMSAccountNo

--Results--
select *
from #lgb2

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-10-17 : 13:07:38

I can't test it since you didn't supply sample data and expected output. But, can you just join to the temp table and add a predicate?

INNER JOIN
	#lbg2 AS Lbg2
	ON <dim_outlet?>.Parentsfid = Lbg2.Parentsfid
WHERE
	Lbg2.report_status <> "Accepted"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources