sql left function

Hi guys and girls

I am new to this particular forum so please be gentle
I need a logic to say whats the postcode in query1, and return the rm in query 2

however i if the rm_sales_band is between 2m-4m, ignore the postcode logic and apply the name TEST,

If the value is 4m+ then apply the postcode logic

Within query 2, pca there can either be 1character or two.

Eg pca

AB = Aberdeen
B = Birmingham


please find attached two screen prints
Query
query2

lookign forward to your help

Three things:
- Your title is not descriptive (Sql left function) and therefore does not meet the forum rules.
- We can't do anything with JPG files. Post a sample Excel worksheet so others can follow
- Could you be more descriptive as your explanation isn't clear?

Others can help with SQL

Please find attached excel file called logic 2
Go to the rawdata sheet, and the yellow highlighted column is a formula, i am trying to replicate this formula but in sql

In sql these are the two, tables where i need to pull the following data

PHP Code: 

SELECT TOP 1000 [ID]
      ,[PCA]
      ,[Area]
      ,[Field_Seller_Less_1M]
      ,[Field_Seller_More_1M]
      ,[ASM]
      ,[RM]
  FROM [FDMS].[dbo].[Geo_PCA_Sellers]


SELECT TOP 1000 [FDMSAccountNo]
      ,[Bank_Chain_No]
      ,[Agent_Chain_No]
      ,[Corp_Chain_No]
      ,[Chain_Chain_No]
      ,[Account_Status]
      ,[Status_Description]
      ,[Account_Status_2]
      ,[RM_Code]
      ,[Open_Date]
      ,[Cancel_Date]
      ,[First_Post_Date]
      ,[Last_Post_Date]
      ,[External_Account_No]
      ,[FDMSAccountNo_First9]
      ,[TB_IND]
      ,[Seller_Code]
      ,[MCC_Code]
      ,[ATV]
      ,[ATV_Vol]
      ,[DBA_Name]
      ,[DBA_Addr1]
      ,[DBA_Addr2]
      ,[DBA_Addr4]
      ,[DBA_City]
      ,[DBA_County]
      ,[Post_Code]
      ,[Open_Year]
      ,[Sales_Band]
      ,[RM_Sales_Band]
      ,[HO]
      ,[SE_Number]
      ,[Legal_Name]
      ,[LBG_Account]
      ,[Alt_Ext_ID]
      ,[LBG_Status]
      ,[TA_Account]
      ,[TA_Name]
      ,[Revenue_Share_Pct]
      ,[RM_Account]
      ,[Boarding_Route]
      ,[ParentID]
      ,[Parent_Name]
      ,[ReportGroup1]
      ,[ReportGroup2]
      ,[ReportGroup3]
      ,[Last_Reprice]
      ,[ISO_Account]
      ,[Traded_Last_3_Mnths]
      ,[Traded_Last_6_Mnths]
      ,[Traded_Last_9_Mnths]
      ,[Traded_Last_12_Mnths]
      ,[Account_Age_Months]
      ,[Active_Outlet_Count]
      ,[Active_Merchant_Count]
  FROM [FDMS].[dbo].[Dim_Outlet] 


So far i have written the code as

PHP Code: 

Select[FDMSAccountNo],
  [HO],
  [RM_Sales_Band],
  [RM_Code],
  [Post_Code],
  [MCC_Code]
  FROM [FDMS].[dbo].[Dim_Outlet]
  where [RM_Sales_Band]in ('2M to 4m','4m +')
  and [HO] = 'Y' 
  and rm_code = 'na'
  And ISO_Account = 'N'
  and FDMSAccountNo not in 
  ( select [TA_MID] from fdms_partnerreporting.tmp.trade_assocations) 


Which is running fine, however i need [RM] FROM [FDMS].[dbo].[Geo_PCA_Sellers]brought accross so i can link the Rm field to the postcode field on the FROM [FDMS].[dbo].[Dim_Outlet]

The only problem as mentioned in the begining post is that some postcodes have one Character and others have two

Hi Kyle further to your knowledge

I have changed the forumla to (see below) and it seems to be running perfectly fine now . Thank you for your support .

Regards

D

PHP Code: 

SELECT sub.*, 
       case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
FROM   
(SELECT[fdmsaccountno], 
              [ho], 
              [rm_sales_band], 
              [rm_code], 
              [post_code], 
              CASE 
                WHEN Isnumeric(RIGHT(LEFT(post_code, 2), 1)) = '0' THEN 
                LEFT(post_code, 2) 
                ELSE LEFT(post_code, 1) 
              END AS 'sPostcode', 
              [mcc_code] 
        FROM   [FDMS].[dbo].[dim_outlet] 
        WHERE  [rm_sales_band]IN ( '2M to 4m', '4m +' ) 
               AND [ho] = 'Y' 
               AND rm_code = 'na' 
               AND iso_account = 'N' 
               AND fdmsaccountno NOT IN (SELECT [ta_mid] 
                                         FROM 
                   fdms_partnerreporting.tmp.trade_assocations)) Sub 
       INNER JOIN [geo_pca_sellers] 
               ON [pca] = spostcode