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.
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_DateJB001 | Jo Bloggs | 90.00 | 01/02/2013JB001 | Jo Bloggs | 25.00 | 02/02/2013JB001 | Jo Bloggs | 25.00 | 01/03/2013MS001 | Mark Smith| 10.00 | 20/01/2013MS001 | Mark Smith| 40.00 | 01/02/2013PB001 | Peter Bob | 98.00 | 10/03/2013PB001 | Peter Bob | 100.00 | 13/03/2013What 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.00Any 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_ValueFROM 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] |
|
|
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 15The multi-part identifier "b.Order_Date" could not be bound.Msg 4104, Level 16, State 1, Line 16The multi-part identifier "b.Order_Date" could not be bound.Msg 4104, Level 16, State 1, Line 18The multi-part identifier "b.Pers_name" could not be bound.Msg 4104, Level 16, State 1, Line 10The 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? |
|
|
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_ValueFROM 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 |
|
|
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_ValueFROM 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 MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
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. |
|
|
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_ValueFROM (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 |
|
|
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 ogroup by convert(Date,dateadd(month,datediff(month,0,o.Ord_Date),0)), o.Pers_Reforder by o.[Order Month], [Sales Rank] desc, o.Pers_Ref CODO ERGO SUM |
|
|
nevzab
Starting Member
34 Posts |
Posted - 2013-06-18 : 12:54:31
|
All,Thanks so much for your help. Got there in the end. |
|
|
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_Dategroup by MONTH(order_date), pers_name) AWhere [Rank] = 1 |
|
|
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_Dategroup by MONTH(order_date), pers_name) AWhere [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 |
|
|
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)) |
|
|
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 monthFor 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... |
|
|
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. |
|
|
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...Indeedsee similar logics herehttp://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|