J Hamaker J Hamaker - 4 months ago 7
SQL Question

Getting correct row results from a Select max() query

Availability table is

| avail_id | emp_id | effective_date | Friday | Saturday | Sunday |
| 0 | 15 | 2016-07-14 | Yes | Yes | No |
| 1 | 15 | 2016-06-14 | Yes | Yes | Yes |
| 2 | 12 | 2016-07-01 | Yes | No | No |
| 3 | 12 | 2016-07-14 | Yes | Yes | Yes |
| 4 | 17 | 2016-05-14 | No | Yes | Yes |
| 5 | 17 | 2016-06-14 | Yes | Yes | Yes |
| 6 | 17 | 2016-07-14 | Yes | No | No |


I have been trying a variety of version of the following query.

SELECT Availability.emp_id, max(Availability.effective_date), Availability.friday, Availability.saturday, Availability.sunday Employees.emp_fname, Employees.emp_lname FROM Availability LEFT JOIN Employees ON Availability.emp_id = Employees.emp_id WHERE Employees.active='Yes' GROUP BY Availability.emp_id ORDER BY Employees.position_id, Employees.emp_lname, Employees.emp_fname, Availability.effective_date DESC


My goal is to select the row with the most recent availability for each employee - and ultimately to be able to select the availability which applies at a given date*. Unfortunately the results I've been getting will be mixing the values from the first row for each employee in the Availability table. For example, it will give me the effective date of 2016-07-14 for employee 15, but will give the other column values which correspond to the 2016-06-14 effective date.

*Employees may submit a new availability for when they start school in 3 weeks, but when running the report for today I would want what applies today, not in 3 weeks. Also, there is no set date for new availability which would apply to all employees.

Answer

Could be you can use in clause on max

SELECT 
    Availability.emp_id
  , Availability.friday
  , Availability.saturday
  , Availability.sunday
  ,  Employees.emp_fname
  , Employees.emp_lname 
FROM Availability 
LEFT JOIN Employees ON Availability.emp_id = Employees.emp_id 
WHERE Employees.active='Yes' 
AND (Availability.emp_id, Availability.effective_date ) in 
        ( select  Availability.emp_id   , max(Availability.effective_date)
           FROM Availability group by Availability.emp_id )
ORDER BY 
      Employees.position_id
    , Employees.emp_lname
    , Employees.emp_fname
    , Availability.effective_date DESC
Comments