Help with data manipulation - 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)

Help with data manipulation

Author

Topic

Phoskins84
Starting Member

2 Posts

Posted - 2008-01-08 : 19:28:19

I am trying to manipulate data based on sales. The parameter that I have to meet is to find the 5 highest salesmen that sold the most in one week. I have the query that pulls the data, I just need to sort the data out. Each week needs to be catagorized by the salesmen whom sold the highest volume that week. Then the salesmen need to be compared against each other to see who had the highest sales ever in one week to find the top 5.

This is a pretty unique problem, I hope I was clear enough!

Thanks in advance!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-08 : 19:35:35

Something like this perhaps?

I am sure you can employ the RANK and OVER BY options to make this much simpler, but I am still on SQL 2000 box so don't have enough practice on 2005 using those functions.

My idea is to get the max SalesTotal for a particular week, and link that dollar amount and week to a table with the SalesmanID's. Join those and query that result for the top 5 ordering by the highest sales...

Not sure if it helps, and this isn't so unique that it will be a problem for some of the experts areound here.


Select Top 5 ws.SalesmanID,hs.HighestSales
FROM (Select WeekNumber,Max(SalesTotal) as HighestSales
      FROM SalesTable 
      Group by Weeknumber) hs  inner join
      (Select Weeknumber, SalesmanID
       FROM SalesTable) ws
                           on hs.Weeknumber = ws.Weeknumber and hs.HighestSales = ws.SalesTotal
Order by HighestSales Desc

Poor planning on your part does not constitute an emergency on my part.

Phoskins84
Starting Member

2 Posts

Posted - 2008-01-08 : 19:57:17

The only issue with the top salestotal for the week is that it can consist of evenly distributed (and often times does) sales between the salesmen. I tried using a WeekNumber naming scheme to create a new column. It converts the MM/DD/YYYY to YYWW, but I cannot figure out how to keep the Employee.ID while joining and adding the Invoice.Amount for each WeekID then using the Top 5 to pick out the weeks that had the highest individual salesmen's value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 23:08:36

Try this:-

SELECT tmp.WeekNumber,tmp.SalesmanID,tmp.HighestSales
FROM
(    
 SELECT ROW_NUMBER() OVER (ORDER BY t.HighestSales DESC) AS 'RowNo',
      t.WeekNumber,
      t.SalesmanID,
      t.HighestSales
 FROM  (Select WeekNumber,
      SalesmanID,
      Max(SalesTotal) as HighestSales
      FROM SalesTable 
      Group by Weeknumber,SalesmanID)t
)tmp
WHERE tmp.RowNo<=5

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-09 : 01:50:47

See what you can do with Row_number() functionhttp://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources