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
 Finding the top salesperson by month

Author  Topic 

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 08:16:13
Hi,

I am trying to find the top sales person for each month this year. I have created a View called Orders_By_Date that returns...


Pers_Ref | Pers_Name | Order_Value | Order_Date
JB001 | Jo Bloggs | 90.00 | 01/02/2013
JB001 | Jo Bloggs | 25.00 | 02/02/2013
JB001 | Jo Bloggs | 25.00 | 01/03/2013
MS001 | Mark Smith| 10.00 | 20/01/2013
MS001 | Mark Smith| 40.00 | 01/02/2013
PB001 | Peter Bob | 98.00 | 10/03/2013
PB001 | Peter Bob | 100.00 | 13/03/2013

What i'd like to return is the following...

Month | Pers_Name | Order_Value
1 | Mark Smith| 10.00
2 | Jo Bloggs | 115.00
3 | Peter Bob | 198.00

Any help would be greatly appreciated, thanks.



James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 08:24:24
[code]SELECT
DATEADD(mm,DATEDIFF(mm,0,a.Order_Date),0) AS MONTH,
b.Pers_name,
b.Order_Value
FROM
Orders_By_Date a
CROSS APPLY
(
SELECT TOP (1)
b.Pers_name,
SUM(b.Order_Value) AS Order_Value
FROM
Orders_By_Date
WHERE
b.OrderDate >= DATEADD(mm,DATEDIFF(mm,0,a.Order_Date),0)
AND b.Order_Date < DATEADD(mm,1+DATEDIFF(mm,0,a.Order_Date),0)
GROUP BY
b.Pers_name
ORDER BY
b.Order_Value DESC
) b

[/code]
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 10:08:41
Hi James,

That doesn't seem to work. I got this error

Msg 4104, Level 16, State 1, Line 15
The multi-part identifier "b.Order_Date" could not be bound.
Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "b.Order_Date" could not be bound.
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "b.Pers_name" could not be bound.
Msg 4104, Level 16, State 1, Line 10
The multi-part identifier "b.Pers_name" could not be bound.

I assumed this was because the view referenced in the sub query did not have an alias of B. When I added the alias the error had gone but the result set was not what I had hoped for, i.e. not the top sales people but many duplicate values. Any ideas?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 10:14:21
A tiny little error with a big impact - see in red below:
SELECT
DATEADD(mm,DATEDIFF(mm,0,a.Order_Date),0) AS MONTH,
b.Pers_name,
b.Order_Value
FROM
Orders_By_Date a
CROSS APPLY
(
SELECT TOP (1)
b.Pers_name,
SUM(b.Order_Value) AS Order_Value
FROM
Orders_By_Date AS b
WHERE
b.OrderDate >= DATEADD(mm,DATEDIFF(mm,0,a.Order_Date),0)
AND b.Order_Date < DATEADD(mm,1+DATEDIFF(mm,0,a.Order_Date),0)
GROUP BY
b.Pers_name
ORDER BY
b.Order_Value DESC
) b
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-18 : 10:18:18
[code]
SELECT
DATEADD(mm,DATEDIFF(mm,0,a.Order_Date),0) AS MONTH,
b.Pers_name,
b.Order_Value
FROM
Orders_By_Date a
CROSS APPLY
(
SELECT TOP (1) WITH TIES
b.Pers_name,
SUM(b.Order_Value) AS Order_Value
FROM
Orders_By_Date AS b
WHERE
b.OrderDate >= DATEADD(mm,DATEDIFF(mm,0,a.Order_Date),0)
AND b.Order_Date < DATEADD(mm,1+DATEDIFF(mm,0,a.Order_Date),0)
GROUP BY
b.Pers_name
ORDER BY
b.Order_Value DESC
) b

[/code]

if you want to take account of ties as well (if exists)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 10:46:50
This no longer errors but as I pointed out in my last message, it does not give me the result set I was expecting. I am getting duplicate salespeople for the same month. I want to see each month listed only once and the top salesperson in that month.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-18 : 11:17:34
Let us try and see if third time is indeed the charm:
SELECT
a.Month,
b.Pers_name,
b.Order_Value
FROM
(SELECT DISTINCT DATEADD(mm,DATEDIFF(mm,0,a.Order_Date),0) AS MONTH FROM Orders_By_Date )a
CROSS APPLY
(
SELECT TOP (1)
b.Pers_name,
SUM(b.Order_Value) AS Order_Value
FROM
Orders_By_Date AS b
WHERE
b.OrderDate >= a.Month
AND b.Order_Date < DATEADD(mm,1,a.Month)
GROUP BY
b.Pers_name
ORDER BY
b.Order_Value DESC
) b
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-06-18 : 11:46:47
This will rank all of your sales people from top to bottom for each month.

select
[Order Month] = convert(Date,dateadd(month,datediff(month,0,o.Order_Date),0)),
o.Pers_Ref,
[Pers_Name] = max(o.Pers_Name),
Order_Value = sum(o.Order_Value),
[Sales Rank] =
rank() over ( partition by convert(Date,dateadd(month,datediff(month,0,o.Order_Date),0)) order by sum(o.Order_Value) desc ))
from
Orders_By_Date o
group by
convert(Date,dateadd(month,datediff(month,0,o.Ord_Date),0)),
o.Pers_Ref
order by
o.[Order Month],
[Sales Rank] desc,
o.Pers_Ref






CODO ERGO SUM
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 12:54:31
All,

Thanks so much for your help. Got there in the end.
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 15:56:22
In case anyone is interested, here is how I resolved it in the end. Thanks again for your help.

select A.Rank,A.Name, A.Month, A.Value from

(select

RANK() over (partition by MONTH(order_date)
order by SUM(order_value) desc) as [Rank]
,MONTH(order_date) as [Month]
,Pers_Name as [Name]
,SUM(order_value) as [Value]

from

Orders_By_Date

group by MONTH(order_date), pers_name) A

Where [Rank] = 1
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2013-06-18 : 16:19:38
quote:
Originally posted by nevzab

In case anyone is interested, here is how I resolved it in the end. Thanks again for your help.

select A.Rank,A.Name, A.Month, A.Value from

(select

RANK() over (partition by MONTH(order_date)
order by SUM(order_value) desc) as [Rank]
,MONTH(order_date) as [Month]
,Pers_Name as [Name]
,SUM(order_value) as [Value]

from

Orders_By_Date

group by MONTH(order_date), pers_name) A

Where [Rank] = 1




You may get incorrect results if you have data for more than one year in the table because you will be grouping together data from multiple years under the month.



CODO ERGO SUM
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-18 : 16:52:07
In this case, the report covers one calendar year max so no problem...
Appreciate your comments though, I am quite new to SQL and I sometimes miss this kind of detail.

Cheers.

p.s. Can you explain in laymen's terms what this does?

convert(Date,dateadd(month,datediff(month,0,o.Order_Date),0))
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-18 : 21:09:15
quote:
Originally posted by nevzab


p.s. Can you explain in laymen's terms what this does?

convert(Date,dateadd(month,datediff(month,0,o.Order_Date),0))



convert(Date,dateadd(month,datediff(month,0,o.Order_Date),0)) returns the first day of the month
For example: If Order_Date = June 15, 2012 it returns: June 1st, 2012;
If Order_Date = January 15, 1930 it returns: January 1st, 1930;
and so on...
Go to Top of Page

nevzab
Starting Member

34 Posts

Posted - 2013-06-19 : 06:14:09
Gotcha! So this allows all orders associated with a particular month to be group by that month but the same month in previous years is grouped separately due to the year being present.

Ta,

Nev.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 06:18:16
quote:
Originally posted by nevzab

Gotcha! So this allows all orders associated with a particular month to be group by that month but the same month in previous years is grouped separately due to the year being present.

Ta,

Nev.


yep...Indeed

see similar logics here

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -