• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 728
  • Last Modified:

TSQL Top records

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.

1 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now