#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] sql left function

## masond3

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

----------


## K m

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?

----------


## masond3

HI 

thank you for your reply 
I am not sure what function i need in sql, in excel it is a left function.

In excel i can provide you with a sample sheet of how the formula works, 
but how do i get info from sql into excel , so you guys can test ?

----------


## K m

Others can help with SQL

----------


## masond3

Thank you  :Smilie:  i wait for the sql guys help me out  :Smilie:

----------


## Kyle123

Can you post your sql so I don't have to type it out?

----------


## masond3

How do you mean post it ? 

i am new to sql ( so sorry for the dumb questions)

----------


## Kyle123

I mean, copy and paste. I'm not typing your query out in a solution when I can copy and paste it from your post  :Smilie:

----------


## masond3

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 




```

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 




```

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

----------


## Kyle123

Maybe:



```

SELECT sub.*,        rm 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 


```

----------


## masond3

Kyle123, You absoulte beauty 

I have been thinking about this for ages  :Smilie:  
You know we spoke about that api with bing maps, do you know if that can be incorporated into sql ?

---------- Post added at 09:38 AM ---------- Previous post was at 09:34 AM ----------

Kyle123- thank you for providing me with a solution, however there is still a step missing. 

I need it to say that if rm_sales_band]IN ( '2M to 4m') than the rm should be Test . if its 4m plus then apply the postcode logic

----------


## Kyle123

How about**:



```

SELECT [fdmsaccountno],        [ho],        [rm_sales_band],        [rm_code],        [post_code],        CASE          WHEN [rm_sales_band] = '2M to 4m' THEN 'Test'          ELSE [rm]        END AS [RM],        [mcc_code], FROM  (SELECT sub.*,               rm        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) 


```

----------


## masond3

i get the error msg 

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FROM'

----------


## Kyle123

It's my typo, delete the last comma on line 10 before the from

----------


## masond3

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





```

SELECT sub.*,        case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rminto #rmtmpFROM   (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 


```

----------

