I posed this question the other day but cant find the original thread so her it is again.
Question:
What query would you construct to generate a report of all the employee names with the date and value of their biggest sale?
Sample data for the Employees table
[TABLE=width: 324]
[TR]
[TD]Employee_key
[/TD]
[TD]Surname
[/TD]
[TD]Forename
[/TD]
[/TR]
[TR]
[TD]54944
[/TD]
[TD]Smith
[/TD]
[TD]Dorothy
[/TD]
[/TR]
[TR]
[TD]108395
[/TD]
[TD]Dolzael
[/TD]
[TD]Alan
[/TD]
[/TR]
[TR]
[TD]150478
[/TD]
[TD]Kerzel
[/TD]
[TD]Brittany
[/TD]
[/TR]
[TR]
[TD]201000
[/TD]
[TD]Jones
[/TD]
[TD]Kevin
[/TD]
[/TR]
[TR]
[TD]244850
[/TD]
[TD]Winstone
[/TD]
[TD]James
[/TD]
[/TR]
[TR]
[TD]255655
[/TD]
[TD]Flenn
[/TD]
[TD]Glen
[/TD]
[/TR]
[/TABLE]
Sample data for the Sales table
[TABLE=width: 415]
[TR]
[TD]i.e. the product that was sold
[/TD]
[TD]i.e. the employee who made the sale
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]|
[/TD]
[TD]|
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Order_key
[/TD]
[TD]Employee_key
[/TD]
[TD]Sale_date
[/TD]
[TD]Value
[/TD]
[/TR]
[TR]
[TD]109887
[/TD]
[TD]54944
[/TD]
[TD]15/06/2012
[/TD]
[TD]2,199.00
[/TD]
[/TR]
[TR]
[TD]216789
[/TD]
[TD]54944
[/TD]
[TD]01/01/2012
[/TD]
[TD]3,502.00
[/TD]
[/TR]
[TR]
[TD]300955
[/TD]
[TD]54944
[/TD]
[TD]31/01/2011
[/TD]
[TD]5,189.00
[/TD]
[/TR]
[TR]
[TD]402000
[/TD]
[TD]108395
[/TD]
[TD]14/05/2009
[/TD]
[TD]2,999.99
[/TD]
[/TR]
[TR]
[TD]511309
[/TD]
[TD]150478
[/TD]
[TD]15/06/2012
[/TD]
[TD]2,199.00
[/TD]
[/TR]
[TR]
[TD]489700
[/TD]
[TD]201000
[/TD]
[TD]01/01/2012
[/TD]
[TD]2,199.00
[/TD]
[/TR]
[TR]
[TD]109887
[/TD]
[TD]244850
[/TD]
[TD]31/01/2011
[/TD]
[TD]2,199.00
[/TD]
[/TR]
[TR]
[TD]109887
[/TD]
[TD]255655
[/TD]
[TD]14/05/2009
[/TD]
[TD]2,199.00
[/TD]
[/TR]
[/TABLE]
This is the query that was proposed but it does not work and I dont know why!!!
SELECT Max(Sales.Value) AS MaxValue, Employee.Forename, Employee.Surname, Sales.sale_date
FROM Sales
INNER JOIN Employee
ON Sales.Employee_key = Employee.Employee_key
GROUP BY Employee.Forename, Employee.Surname, Sales.Employee_Key, Sales.sale_date
It returns the following:-
2999.99 Alan Dolzael 2009-05-14
2199.00 Brittany Kerzel 2012-06-15
5189.00 Dorothy Smith 2011-01-31
3502.00 Dorothy Smith 2012-01-01
2199.00 Dorothy Smith 2012-06-15
2199.00 Glen Flenn 2009-05-14
2199.00 James Winstone 2011-01-31
2199.00 Kevin Jones 2012-01-01
It is wrong because it returns 3 rows for Dorothy Smith as opposed to just the one row with the maximum sales value, as required by the original question.
Any ideas how the query needs to be modifed to return only the maximum sales.value for all employees?
I should mention that I am running this query in MSSQL Server 2012.
