Results 1 to 3 of 3

Thread: row number help

  1. #1
    Join Date
    Aug 2012
    Posts
    18

    Unanswered: row number help

    Hey guys

    i need some help with sampling data


    Please see my query below
    Please find attached screen print of the results once the query has been excuted

    However i have an issue, as you can see from my screen print i have multiple 2m- 4m accounts. I want to assign these '2M to 4M' equally between two people.

    For eg, if there is 100accounts within the '2M to 4M', i want 50 of those accounts to go to seller r14 and the others to go to r15.

    Can anyone help me with this



    SELECT sub.*,

    case when SalesBandRM = '2M to 4M'
    and MCC_Code not in ('7997','7941') then 'R14'
    when fdmsaccountno IN ('878177270880','878030700883','878231021881','878 233596880','878970059886','878970013883') OR MCC_Code in ('7997','7941')
    then 'R05'

    else RMSC end as RMSC

    FROM
    (SELECT
    [DBA_Name],
    o.[fdmsaccountno],
    [ho],
    sum ([Gross_Sales]) as Sales,
    dbo.salesbandRM(sum(Gross_Sales))as SalesBandRM,
    [post_code],
    [Open_Date],



    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].[Fact_Financial_History_Annualised] f
    inner join Dim_Outlet o
    on f.FDMSAccountNo = o.FDMSAccountNo
    WHERE [rm_sales_band]IN ( '2M to 4m', '4m +' )
    AND [ho] = 'Y'
    and Account_Status = '16'
    and LBG_Status <> 'Accepted'
    AND iso_account = 'N'
    AND Open_Date < dateadd(mm, -3, getdate())
    and Agent_Chain_No not in ('878970059886', '878970013883')
    AND o.fdmsaccountno NOT IN (SELECT [ta_mid]
    FROM
    fdms_partnerreporting.tmp.trade_assocations)

    group by
    [DBA_Name],
    o.[fdmsaccountno],
    [ho],
    [post_code],
    [Open_Date],
    [MCC_Code]

    ) Sub
    INNER JOIN [geo_pca_sellers]
    ON [pca] = spostcode



    order by DBA_Name
    Attached Thumbnails Attached Thumbnails query1.JPG  

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

    I think you can try to adapt the following query:

    Code:
    with CTE as
    (
        select 
            t.*,
            ROW_NUMBER() OVER(ORDER BY OrderColumn) AS RowNum,
            COUNT(*) OVER() AS Tot 
        FROM MyTable as t
    )
    
    select 
        c.*,
        (case when c.RowNum > c.Tot / 2 then 'B' else 'A' end) as MyColumn
    from CTE as c
    Hope it's useful.

  3. #3
    Join Date
    Aug 2012
    Posts
    18
    Hi Imex

    Thank you for your answer, is it possible if you can post your example up with my query, so i can see where it sits ?

Posting Permissions

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