Results 1 to 6 of 6

Thread: left help

  1. #1
    Join Date
    Aug 2012
    Posts
    18

    Unanswered: left help

    Hi guys and girls

    I need some more help to match different tables together
    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
    Attached Thumbnails Attached Thumbnails query2.JPG   query.JPG  

  2. #2
    Join Date
    Aug 2012
    Posts
    18
    any ideas anyone ?

  3. #3
    Join Date
    Aug 2012
    Posts
    18
    Hi have the forumla working in excel, however i need it transfered to sql

    if any of you have any questions please get back to me

    Many thanks

    D

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though, the following quoted article came from Oracle forum,
    it would be just a good recommendation for you, too.

    Quote Originally Posted by kordirko View Post
    It would be helpful if you ... provided instructions for create tables and fill them with sample data,
    instead of attaching pretty looking images. Just like this:
    Code:
    CREATE TABLE bridge AS
    SELECT 'Fr1' FlightRouteID , 'F1' FlightID, 1 SequenceIndex FROM dual UNION
    SELECT 'Fr1', 'F1', 1 FROM dual UNION
    SELECT 'Fr2', 'F1', 1 FROM dual UNION
    SELECT 'Fr2', 'F8', 2 FROM dual UNION
    SELECT 'Fr3', 'F2 ', 1 FROM dual UNION
    SELECT 'Fr3', 'F8', 2 FROM dual UNION
    SELECT 'Fr4', 'F1', 1 FROM dual UNION
    SELECT 'Fr4', 'F5', 2 FROM dual UNION
    SELECT 'Fr5', 'F1', 1 FROM dual UNION
    SELECT 'Fr5', 'F5', 2 FROM dual UNION
    SELECT 'Fr5', 'F5', 2 FROM dual UNION
    SELECT 'Fr5', 'F6', 3 FROM dual UNION
    SELECT 'Fr6', 'F1', 1 FROM dual UNION
    SELECT 'Fr6', 'F5', 2 FROM dual UNION
    SELECT 'Fr6', 'F6', 3 FROM dual UNION
    SELECT 'Fr6', 'F7', 4 FROM dual UNION
    SELECT 'Fr7', 'F1', 1 FROM dual UNION
    SELECT 'Fr7', 'F5', 2 FROM dual UNION
    SELECT 'Fr7', 'F8', 3 FROM dual 
    ;
    Having sample data people can easy recreate and play with your case on their computers and give you back working solutions,
    but not many would be willing to tiresomly type char by char from images.

    ...

  5. #5
    Join Date
    Aug 2012
    Posts
    18
    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 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


    Which is providing me with rm name based on the postcode. however i need to add an addtional functionality to say that if the rm_sales_band]IN ( '2M to 4m') then the postcode logic shouldnt work, and the rm column should say TEST.

    hoping this helps

  6. #6
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    SELECT 
        sub.*, 
        case when [rm_sales_band] = '2M to 4m'
            then 'TEST'
            else rm 
        end as 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 
    LEFT JOIN [geo_pca_sellers] ON [pca] = spostcode
    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •