SQL statement that would select top 10 salesmen ba

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

General SQL Server Forums

New to SQL Server Programming

SQL statement that would select top 10 salesmen ba

Author

Topic

Shaz247
Starting Member

1 Post

Posted - 2011-02-07 : 01:24:09

I have the following fields in the database.
Salesman, , Region, Branch, Product, Charge, Quantity, Month
I need to write a SQL statement/query to be used in SSIS that would select the top 10 salesmen, with its corresponding top 10 products sold based on Monetary value within the latest, most recent month and display the previous two months for information purposes as well. This has to be done for each branch.
The same must be done for number of products sold.
Alternatively, instead of top 10 based on current month, base it on the year to date and only display the last three months.
Could anyone help me with this query or statement? We are using Sql Server 2005.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2011-02-07 : 02:06:28

please provide the table structure with data...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-07 : 04:24:14

This should get you on the right track at least:

SELECT * 
FROM (
	SELECT Salesman, Region, Branch, Product, MonthNum = MONTH(DateColumn),
		RowNum = ROW_NUMBER() OVER (PARTITION BY MONTH(DateColumn), Salesman, Region, Branch, Product ORDER BY SUM(Charge) DESC)
		SumCharge = SUM(Charge),
		SumQuantity = SUM(Quantity)
	WHERE DateColumn > DATEADD(month,DATEDIFF(month,0,GETDATE())-3,0)
	GROUP BY Salesman, Region, Branch, Product, MONTH(DateColumn)
	) AS a
WHERE RowNum >= 10

- Lumbago
My blog-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources