Date Query confusion.

[daffelito]

Hi, I have a problem with a query that's going to show the latest dates.

First i got a table with 4 columns
1. ID (Primarykey)
2. WorkplaceID (Child to Primarykey in other table)
3. DateNeed
4. DateDone

This table is storing information when a workplace needs to be cleaned and when it's been cleaned.
When a user finds the workplace and marks it as Needs to bee cleaned, it stores the Now date in the DateNeed column, and when its been cleaned it stores the Now date in the DateDone column.

The thing is that these workplaces needs to be cleaned maby 1 - 3 times a week depending on other processes. So a workplaceID with dates can be stored several times in the table.

So what i need is a query that only shows the row with the latest date for the workplaces. If the date in the DateNeed column is more recent it should not be shown.
I have strugled with this for some time now but when i use the SELECT MAX i get all rows but grouped by the latest date on top. I dont want all the dates for a workplace to be shown only the latest.

Please help, i soon don't got any temper left.

 

[r937]

SELECT t.ID
     , t.WorkplaceID 
     , t.DateNeed
     , t.DateDone
  FROM daTable AS t
INNER
  JOIN ( SELECT WorkplaceID
              , MAX(DateNeed) AS MaxNeed
           FROM daTable
         GROUP
             BY WorkplaceID ) AS m
    ON m.WorkplaceID = t.WorkplaceID
   AND m.MaxNeed = t.DateNeed

r937.com | rudy.ca

 

[daffelito]

Thanks, i will try this. Thank you verry much. It would be so nice to see this work...