Top Sales person - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Top Sales person

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2009-05-08 : 00:25:48

I want to find Top and Bottom 10% salesperson based on their sales. How to find?

DECLARE @Sales TABLE
(
SalesPersonID varchar(10), TotalSales int
)

INSERT @Sales
SELECT 1, 200 UNION ALL
SELECT 2, 300 UNION ALL
SELECT 7, 300 UNION ALL
SELECT 4, 100 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 5, 600 UNION ALL
SELECT 2, 200 UNION ALL
SELECT 5, 620 UNION ALL
SELECT 4, 611 UNION ALL
SELECT 3, 650 UNION ALL
SELECT 7, 611 UNION ALL
SELECT 9, 650 UNION ALL
SELECT 3, 555 UNION ALL
SELECT 9, 755 UNION ALL
SELECT 8, 650 UNION ALL
SELECT 3, 620 UNION ALL
SELECT 5, 633 UNION ALL
SELECT 6, 720
GO

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-08 : 00:41:43

[code]
select top 10 percent * from @Sales order by TotalSales desc
select top 10 percent * from @Sales order by TotalSales asc
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lols
Posting Yak Master

174 Posts

Posted - 2009-05-08 : 00:44:40

I want to use CTE and also add a department column. can i write same query to find top 10% and bottom 10% in each department? I

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-08 : 01:36:57

Yes you can.

;WITH Yak
AS (SELECT *, ntile(10) over (order by totalsales) as a,
ntile(10) over (order by totalsales desc) AS b
)

SELECT * from yak where 1 in (a, b)

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-08 : 01:59:09

quote:
Originally posted by Peso

Yes you can.

;WITH Yak
AS (SELECT *, ntile(10) over (order by totalsales) as a,
ntile(10) over (order by totalsales desc) AS b
)

SELECT * from yak where 1 in (a, b)

E 12°55'05.63"
N 56°04'39.26"

In SQLTeam, You are the one who mostly use Value in (col1,col2,...) syntax
You can add your opinion in this post
http://sqlblog.com/blogs/denis_gobo/archive/2009/04/09/13186.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-08 : 02:13:35

More on IN here
http://weblogs.sqlteam.com/peterl/archive/2007/09/28/Save-some-time-and-key-typing.aspx

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources