+ Post New Thread
Results 1 to 2 of 2
Windows Thread, sql query question in Technical; I posed this question the other day but cant find the original thread so her it is again. Question: What ...
  1. #1

    Join Date
    Aug 2012
    Location
    london
    Posts
    1
    Thank Post
    1
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    sql query question

    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
    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
    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.
    Last edited by ajc001; 16th August 2012 at 12:02 AM.

  2. #2

    CESIL's Avatar
    Join Date
    Nov 2006
    Location
    Hampshire
    Posts
    1,394
    Thank Post
    108
    Thanked 266 Times in 197 Posts
    Rep Power
    168
    Try this
    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;

SHARE:
+ Post New Thread

Similar Threads

  1. SQL Query Using Full Text Engine inSQL 2005
    By Phanikumar in forum Web Development
    Replies: 0
    Last Post: 22nd September 2008, 08:47 AM
  2. sims sql upgrade question
    By Uraken in forum MIS Systems
    Replies: 10
    Last Post: 18th March 2008, 03:18 PM
  3. SQL Query :S
    By Kained in forum Web Development
    Replies: 10
    Last Post: 25th July 2007, 02:56 AM
  4. C#/SQL 2005 question
    By Gatt in forum Coding
    Replies: 4
    Last Post: 29th June 2007, 05:12 AM
  5. Active Directory saved queries question.
    By tosca925 in forum Windows
    Replies: 4
    Last Post: 2nd May 2007, 03:17 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •