Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012
    Posts
    18

    Unanswered: returning results based in one query, into an existing query

    HI guys

    i need some help linking two tables together to get an end result

    Please find below the code i have used

    The first part of the query provides me with the info i need


    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
    select * from #rmtmp


    i have created a second query

    Which looks at all all mccs which r seller code have dealt with and gives highest seller code which deals with that particular mcc

    and the query is

    with
    CTE_Group as
    (
    select seller_code, mcc_code, count(mcc_code) as mcc_count
    from [FDMS].[dbo].[Dim_Outlet]
    where ( Rm_Sales_Band = '2M to 4m' or Rm_Sales_Band = '4m +' ) and
    (Seller_Code like 'r%') group by seller_code, mcc_code
    ),



    CTE_RowNum as
    (
    select
    c.*,
    ROW_NUMBER() OVER(PARTITION BY mcc_code
    ORDER BY mcc_count desc) as RowNum
    from CTE_Group c
    )

    select * from CTE_RowNum
    where RowNum = 1
    order by MCC_Code desc,mcc_count desc


    What i need from here is the seller code linked to that particular mcc code, brought over to the original query. If the following seller codes are not assigned to a particular mcc can you populate N.A
    R05
    R10
    R12
    R13

    Please find attached three images
    query1-
    query2
    ideal results

    From the ideal results, image i only need the seller code brought over
    Attached Thumbnails Attached Thumbnails query2.JPG   query1.JPG   ideal results.JPG  

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

Posting Permissions

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