• 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.

Thanks!
0
d1cjm1ex
Asked:
d1cjm1ex
1 Solution
 
lwadwellCommented:
something like this?
declare @salenumbers  table (
    SalesPerson     varchar(20),
    SalesAmt        decimal(12,2),
    Year            int)
insert into @salenumbers values
('X01',150,2012),
('X02',150,2012),
('X03',160,2012),
('X04',104,2012),
('X05',220,2012),
('X06',150,2012),
('X07',150,2012),
('X08',160,2012),
('X09',104,2012),
('X10',220,2012),
('X11',104,2012),
('X12',220,2012),
('X13',150,2012),
('X14',150,2012),
('X15',160,2012),
('X16',104,2012),
('X17',220,2012),
('X18',230,2012),
('X19',190,2012),
('X20',100,2012),
('X21',120,2012),
('X22',111,2012),
('X23',235,2012)

;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

0

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