| Author |
 Topic  |
|
|
Teareal
Starting Member
3 Posts |
 Posted - 05/04/2012 : 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
USA
15 Posts |
Posted - 05/04/2012 : 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 - 05/04/2012 : 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
India
47099 Posts |
Posted - 05/05/2012 : 01:24:17
|
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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Selecting the most sales
