Solved

SQL Server 2005 - How to query 3 top sales persons

Posted on 2013-05-22
19
373 Views
Last Modified: 2013-05-22
Hi.. I have a table of SALES data. It looks like this.
I need to return the Top 3 sales persons in each state.  
I also need to return the names in a single row like this.

CA            BOB, JANE,JON

any idea?
thanks


STATE       SALES PERSON           AMOUNT
CA              JON                           100
CA             JANE                           200
CA            BOB                            500
CA             KAREN                         50
VA              MARK                         10
VA              FRED                         100
VA             RALPH                       600
VA              ROB                            30
0
Comment
Question by:JElster
  • 7
  • 6
  • 6
19 Comments
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 200 total points
ID: 39187122
Hi i knocked this example up for you

http://sqlfiddle.com/#!3/d7c66/3

With CTE As (
 Select * from (Select 
* ,
Rank() OVER (PARTITION BY  state order by amount desc) as Ranked
from Table_name
) Temp1 
Where ranked in (1,2,3)
)
Select distinct 
State,
STUFF(
               (SELECT      ',' + SubTableUser.Salesperson 
               FROM      CTE AS SubTableUser
               WHERE      SubTableUser.STATE = CTE.STATE
                
               FOR XML PATH('')), 1, 1, '') AS Comments
from CTE

Open in new window


Create table TABLE_NAME 
(STATE varchar(30),SALESPERSON varchar(30),AMOUNT int) 


INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','JON','100');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','JANE','200');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','BOB','500');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('CA  ','KAREN','50');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','MARK','10');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','FRED','100');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','RALPH','600');
INSERT INTO TABLE_NAME(STATE,SALESPERSON,AMOUNT) VALUES('VA  ','ROB','30');

Open in new window

0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187129
Probably a better way to do it but this is what came to mind...

Declare @SALES1 varchar(20)
Declare @SALES2 varchar(20)
Declare @SALES3 varchar(20)

set @SALES1 = select top 1 SALESPERSON from SALES 
set @SALES2 = select top 1 SALESPERSON from SALES where SALESPERSON <> @SALES1
set @SALES3 = select top 1 SALESPERSON from SALES where SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2

select (@SALES1 + ',' + @SALES2 + ',' + @SALES3)

Open in new window

0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187165
jpgobert:

your query doesn't take into account the state or amount so all it does in essence is a select the top 3 and concat it on one line...
0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187183
My bad I forgot to add the where... sorry about that...

I'll re-post an update shortly...
0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187203
Better?  Yours definitely is several grades higher than me but I'm no DBA...

Just selecting the final dataset from the temp table... not sure what the OP wants to do with it once it is compiled...

Declare @State varchar(2)
Create Table #tmpSALES (State varchar(2), SalesPeople varchar(60))

Declare State_Cursor Cursor for
Select distinct state from SALES

Open State_Cursor
Fetch Next From State_Cursor into @State
While @@Fetch_Status = 0
Begin

	Declare @SALES1 varchar(20)
	Declare @SALES2 varchar(20)
	Declare @SALES3 varchar(20)

	set @SALES1 = select top 1 SALESPERSON from SALES where State = @State
	set @SALES2 = select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1
	set @SALES3 = select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2

	Insert Into #tmpSALES values (@State, (@SALES1 + ',' + @SALES2 + ',' + @SALES3))

Fetch Next from State_Cursor into @State
End

Close State_Cursor
Deallocate State_Cursor

Select * from #tmpSALES

Drop Table #tmpSALES

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 39187212
Hi..
I'm getting the following

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'With'.
Msg 195, Level 15, State 10, Line 4
'Rank' is not a recognized function name.
Msg 170, Level 15, State 1, Line 16
Line 16: Incorrect syntax near 'XML'.

Using Sql Server 2008

thanks
0
 
LVL 1

Author Comment

by:JElster
ID: 39187215
I think the version of SQL Server is 2005.
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187224
ah right i just double checked that it worked on sql 2005 and it does so can you post your code and i give it a quick look
0
 
LVL 1

Author Comment

by:JElster
ID: 39187234
Sorry it maybe sql 2000... not really sure.. I'm connected remotely.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187239
Ahh now that makes sense to the rank and xml functions not working

run
select  @@Version

Open in new window


if it sql 2000 my code won't work at all.....
0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187246
mine will
0
 
LVL 1

Author Comment

by:JElster
ID: 39187248
Yeah.. it's 2000
Microsoft SQL Server  2000 - 8.00.2066 (Intel X86)   May 11 2012 18:41:14   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
0
 
LVL 1

Author Comment

by:JElster
ID: 39187250
set @SALES1 = select top 1 SALESPERSON from SALES where State = @State


Get
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'select'.
0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187258
My bad... the select statement needs to be enclosed in ()

set @SALES1 = (select top 1 SALESPERSON from SALES where State = @State)
set @SALES2 = (select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1)
set @SALES3 = (select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2)

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 39187395
Hi..
It does not return anything if only 1 sales person... any ideas?
thx again
0
 
LVL 8

Expert Comment

by:jpgobert
ID: 39187441
Are you getting values otherwise?
0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187445
I'm pondering.... mind set is for sql 2005 + thou ....eeeekkkk
0
 
LVL 8

Accepted Solution

by:
jpgobert earned 300 total points
ID: 39187510
Ok... just built a quick test table and I see what you mean.  We just need to add isnull to the setting of the sales variables.  See below.

Declare @State varchar(2)
Create Table #tmpSALES (State varchar(2), SalesPeople varchar(60))

Declare State_Cursor Cursor for
Select distinct state from SALES

Open State_Cursor
Fetch Next From State_Cursor into @State
While @@Fetch_Status = 0
Begin

	Declare @SALES1 varchar(20)
	Declare @SALES2 varchar(20)
	Declare @SALES3 varchar(20)

	set @SALES1 = ISNULL((select top 1 SALESPERSON from SALES where State = @State),'')
	set @SALES2 = ISNULL((select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1),'')
	set @SALES3 = ISNULL((select top 1 SALESPERSON from SALES where State = @State and SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2),'')

	Insert Into #tmpSALES values (@State, (@SALES1 + ',' + @SALES2 + ',' + @SALES3))

Fetch Next from State_Cursor into @State
End

Close State_Cursor
Deallocate State_Cursor

Select * from #tmpSALES

Drop Table #tmpSALES

Open in new window

0
 
LVL 7

Expert Comment

by:Ross Turner
ID: 39187633
This is another version that should graft just using loops rather than cursor

DROP TABLE #TempTable 
Create Table #TempTable
([ID] int Identity,
 [State] Varchar(30)
 )
DROP TABLE #TempHolding 
Create Table #TempHolding
([ID] int Identity,
 [State] Varchar(500),
 [Details] Varchar(500)
 )

Insert into #TempTable
SELECT DISTINCT STATE FROM [dbo].[TABLE_NAME]

Declare @SALES1 varchar(20)
Declare @SALES2 varchar(20)
Declare @SALES3 varchar(20)
Declare @State varchar(20)
DECLARE @Loop int 
Set @Loop = 1



While  @Loop <= (select count(*) from #TempTable)
Begin
Set  @SALES1 = (SELECT TOP 1 SALESPERSON FROM [dbo].[TABLE_NAME] WHERE STATE = (select state from #TempTable where id = @loop) ORDER BY [State],[Amount] DESC)
Set  @SALES2 = (SELECT TOP 1 SALESPERSON FROM [dbo].[TABLE_NAME] WHERE STATE = (select state from #TempTable where id = @loop) and SALESPERSON <> @SALES1 ORDER BY [State],[Amount] DESC)
Set  @SALES3 = (SELECT TOP 1 SALESPERSON FROM [dbo].[TABLE_NAME] WHERE STATE = (select state from #TempTable where id = @loop) and SALESPERSON <> @SALES1 and SALESPERSON <> @Sales2 ORDER BY [State],[Amount] DESC)
SET	 @State  = (select state from #TempTable where id = @loop)
Insert into #tempholding 
values(@State,@SALES1 + ',' + @SALES2 + ',' + @SALES3) 
SET  @loop	= @loop + 1
End

Select * from #tempholding

Open in new window

0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

760 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

19 Experts available now in Live!

Get 1:1 Help Now