Selecting the most sales - 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

General SQL Server Forums

New to SQL Server Programming

Selecting the most sales

Author

Topic

Teareal
Starting Member

3 Posts

Posted - 2012-05-04 : 19:23:53

I am using SQL Server 2008 R2 and I have a DB with 4 tables (Customer, SalesPerson, Vehicle, and SalesPerson_Vehicle_Bridge) I am trying to determine which sales person has the most sales in a given month. The SalesPerson table has fields: Sales_Person_ID, Name, and a couple other irrelevant to this. The Vehicle table has: VIN, Purchase_Date and a few others that are irrelevant also. The SalesPerson_Vehicle_Bridge table has: Sales_Person_ID, VIN, and Profit. I am not sure where to even begin to determine which salesperson has the most sales for a given month. Any advice would be greatly appreciated.

RL
Starting Member

15 Posts

Posted - 2012-05-04 : 20:05:52

Based on the information you have provided, the following query should provide a list of unit sales and total profit by year/month and sales person. For each year/month, the sales person with the highest unit sales for the month is listed first, and the rest in descending order (DESC in the ORDER BY clause).

To list just the top unit sales for March, 2011, you would add WHERE YEAR(V.Purchase_Date) = 2012 AND MONTH(V.Purchase_Date) = 3, and instead of SELECT use SELECT TOP 1.

NOTE: The DISTINCT is not necessary if there's only one row per sales person per VIN in the Sales_Person_Vehicle_Bridge table; sometimes systems like this record multiple records for the same overall transaction.

SELECT
YEAR(V.Purchase_Date) AS year_num,
MONTH(V.Purchase_Date) AS month_num,
SP.Sales_Person_ID AS sales_id,
SP.Name AS sales_name,
COUNT(DISTINCT V.VIN) AS units_sold,
SUM(SPVB.Profit) AS total_profit

FROM
SalesPerson SP INNER JOIN Sales_Person_Vehicle_Bridge SPVB
ON SP.Sales_Person_ID = SPVB.Sales_Person_ID

INNER JOIN Vehicle V
ON V.VIN = SPVB.VIN

GROUP BY
YEAR(V.Purchase_Date),
MONTH(V.Purchase_Date),
SP.Sales_Person_ID,
SP.Name

ORDER BY
1, 2, 5 DESC, 4;

Teareal
Starting Member

3 Posts

Posted - 2012-05-04 : 20:12:43

Cool thanks. I just have to clean it up a bit so that it only displays the top seller for the specific month AS 'Winner". thanks for the help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 01:24:17

[code]
DECLARE @MonthDate datetime

SELECT TOP 1 WITH TIES sp.Name
FROM SalesPerson sp
INNER JOIN SalesPerson_Vehicle_Bridge svb
ON svb.Sales_Person_ID = sp.Sales_Person_ID
INNER JOIN Vehicle v
ON v.VIN = svb.VIN
WHERE v.Purchase_Date >= DATEADD(mm,DATEDIFF(mm,0,@date),0)
AND v.Purchase_Date < DATEADD(mm,DATEDIFF(mm,0,@date)+1,0)
GROUP BY sp.Name
ORDER BY COUNT(*) DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources