TSQL Top records

Posted on 2012-09-09
Last Modified: 2012-09-09
I need a way to display the people who make up the top 20% of sales.

I have a table defined as:  SalesPerson, SalesAmt, Year

For the Year 2012 the total SalesAmt is 100,000 for all SalesPersons.  20% of that is 20,000.  

I want to display the top SalesPersons(s) by their SalesAmt who make up that 20,000.

Question by:d1cjm1ex
    1 Comment
    LVL 25

    Accepted Solution

    something like this?
    declare @salenumbers  table (
        SalesPerson     varchar(20),
        SalesAmt        decimal(12,2),
        Year            int)
    insert into @salenumbers values
    ;with ranked as (
    select *
         , row_number()over(partition by Year order by salesAmt desc) as rn
         , sum(SalesAmt)over(partition by Year) as tot_year_sales
    from @salenumbers
    ), recur_cte as (
    select SalesPerson, SalesAmt, Year, rn, tot_year_sales, SalesAmt as accumSales
    from ranked
    where rn = 1
    union all
    select ranked.SalesPerson, ranked.SalesAmt, ranked.Year, ranked.rn, ranked.tot_year_sales
         , cast(ranked.SalesAmt+recur_cte.accumSales as decimal(12,2)) as accumSales
    from ranked
    inner join recur_cte on ranked.rn = recur_cte.rn+1 and ranked.year = recur_cte.year
    where ranked.SalesAmt+recur_cte.accumSales <= ranked.tot_year_sales*0.2
    select *
    from recur_cte

    Open in new window


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now