inner join help

[masond3]

Hey guys

I need some help
i have created a query which has returned the following columns

ParentID,
Dba_Name,
Post_Code,
HO,
Sales_Annualised,
SalesBanding,
Actual_SalesYTD,
Rolling_12

From here, i need to do an inner join onto my [FDMS].[dbo].[Geo_PCA_Sellers] table

Within the [FDMS].[dbo].[Geo_PCA_Sellers] table it holds the following columns
PCA RMSC
AB R13
AL R17
B R13
BA R17
BB R13
BD R13
,[PCA]
,[RMSC]

The column PCA is abbrevations of the postcode, for eg BS11 0YB is an actual postcode, But instead of having full postcodes within my [Geo_PCA_Sellers] table, i have shortened them down.
For eg, PCA holds BS, BS is short for bristol

RMSC holds the manager of each postcode

I need to do a look up on the postcode from my current results onto the [FDMS].[dbo].[Geo_PCA_Sellers]table, then once the abbreviation of the postcode has been Found in column PCA, return The value in RMSC

Eg of [FDMS].[dbo].[Geo_PCA_Sellers] table layout














I think i need to apply Logic
CASE
WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN
LEFT(post_code, 2)
ELSE LEFT(post_code, 1)


My query is


select ParentID,
SUM(Gross_Sales) as Sales_Annualised,
[FDMS].[dbo].[SalesBandRM](Sum(gross_sales)) as SalesBanding
Into #RM
--drop table #RM
--select * from #RM
from [FDMS].[dbo].[Dim_Outlet] o
inner join [FDMS].[dbo].[Fact_Financial_History_Annualised] fh on o.FDMSAccountNo = fh.FDMSAccountNo
group by ParentID
having SUM (fh.Gross_Sales) > 1999999
order by SUM(Gross_Sales) desc

----------------------------------------------------------------------
select
rm.ParentID,
Dba_Name,
Post_Code,
HO,
LBG_Account,
LBG_Status,
Account_Status,
Sales_Annualised,
SalesBanding
into #test
From #RM rm inner join [FDMS].[dbo].[Dim_Outlet] o on o.FDMSAccountNo = rm.ParentID
where LBG_Status <> 'accepted'
and Account_Status ='16'
AND iso_account = 'N'
AND Open_Date < dateadd(mm, -3, getdate())
and Agent_Chain_No not in ('878970059886', '878970013883')
AND fdmsaccountno NOT IN (SELECT [ta_mid] FROM fdms_partnerreporting.tmp.trade_assocations)
and Sales_Annualised > 1999999
order by DBA_Name

--select * from #test
---------------------------------------------------------------------------

Declare @dateYTD varchar(10)
set @dateYTD = (select year(MAX(hst_date_processed))
from fdms.dbo.Fact_Financial_History)
SELECT ParentID,
sum([hst_sales_amt]) as Actual_SalesYTD
Into #YTD
FROM [FDMS].[dbo].[Fact_Financial_History] f inner join dim_outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where year(hst_date_processed) = @dateYTD
group by o.ParentID
--Drop table #YTD
--drop table #test
--select * from #YTD

---------------------

--Rolling 12 Months --
Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT ParentID
,sum([hst_sales_amt]) as Rolling_12
Into #Rolling12
FROM [FDMS].[dbo].[Fact_Financial_History] f full outer join Dim_Outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where hst_date_processed > @date
group by o.ParentID

--drop table #Rolling12
-------------

------------------------

select
#test.ParentID,
Dba_Name,
Post_Code,
HO,
Sales_Annualised,
SalesBanding,
Actual_SalesYTD,
Rolling_12

from #test
inner join #YTD on #test.ParentID = #YTD.ParentID
inner join #Rolling12 on #test.ParentID =#Rolling12.ParentID

order by Dba_name

--Drop table #RM,#YTD,#Rolling12,#test

[Leizure]

What's the question?