Solved

# TSQL Top records

Posted on 2012-09-09
685 Views
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
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
('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
``````
0

## Featured Post

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.