I posed this question the other day but cant find the original thread so her it is again.
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
Employee_key Surname Forename 54944 Smith Dorothy 108395 Dolzael Alan 150478 Kerzel Brittany 201000 Jones Kevin 244850 Winstone James 255655 Flenn Glen
Sample data for the Sales table
i.e. the product that was sold i.e. the employee who made the sale | | Order_key Employee_key Sale_date Value 109887 54944 15/06/2012 2,199.00 216789 54944 01/01/2012 3,502.00 300955 54944 31/01/2011 5,189.00 402000 108395 14/05/2009 2,999.99 511309 150478 15/06/2012 2,199.00 489700 201000 01/01/2012 2,199.00 109887 244850 31/01/2011 2,199.00 109887 255655 14/05/2009 2,199.00
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
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.
Last edited by ajc001; 16th August 2012 at 12:02 AM.
Code:SELECT DISTINCTROW Max(sales.Value) AS [Max Of Value], employee.Forename, employee.Surname, First(sales.Sale_date) AS [First Of Sale_date]FROM employee LEFT JOIN sales ON employee.Employee_key = sales.Employee_key GROUP BY employee.Forename, employee.Surname;
There are currently 1 users browsing this thread. (0 members and 1 guests)