SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Finding the top salesperson by month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/18/2013 :  08:16:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 06/18/2013 :  08:24:24  Show Profile  Reply with Quote
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
		
		
Go to Top of Page

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/18/2013 :  10:08:41  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 06/18/2013 :  10:14:21  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/18/2013 :  10:18:18  Show Profile  Reply with Quote

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



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

United Kingdom
34 Posts

Posted - 06/18/2013 :  10:46:50  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 06/18/2013 :  11:17:34  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 06/18/2013 :  11:46:47  Show Profile  Reply with Quote
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

United Kingdom
34 Posts

Posted - 06/18/2013 :  12:54:31  Show Profile  Reply with Quote
All,

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

nevzab
Starting Member

United Kingdom
34 Posts

Posted - 06/18/2013 :  15:56:22  Show Profile  Reply with Quote
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)

USA
7020 Posts

Posted - 06/18/2013 :  16:19:38  Show Profile  Reply with Quote
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

United Kingdom
34 Posts

Posted - 06/18/2013 :  16:52:07  Show Profile  Reply with Quote
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

547 Posts

Posted - 06/18/2013 :  21:09:15  Show Profile  Reply with Quote
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

United Kingdom
34 Posts

Posted - 06/19/2013 :  06:14:09  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 06/19/2013 :  06:18:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000