Select only LATEST date of each type for each store location

[Viking]

Wait, I may have figured this out... if not, I will re-post. (can't seem to delete this post)

Ok, I did figure this out, so never mind! :-) I was able to use a CTE to solve it.

[Vectorspace]

Any chance you can re-post your original question, and the solution you found?

That way if anyone else has the same issue in the future they can find the solution here.

[Viking]

Here was my original question:

I currently have the following SQL which finds upcoming (within the next 2 months) lease expiration (LE) and lease option (LO) dates for each store location:

Select RESTORE, REDATETYPE, REDATE
             From FRESTOR02
             Where REDATE between Current_Date and Current_Date + 2 months
               and REDATETYPE in ('LE', 'LO')
             Order By RESTORE

Then I send a notification of the upcoming lease expiration or lease option date for each affected store.

The only problem is that when someone then renews a lease or lease option date like they should do, thereby creating a new LE and/or LO future date for that store in file FRESTOR02, my SQL still finds the original upcoming date and keeps warning them for the next two months until we pass that date. So what I need is to only check the LATEST date record of each type (LE and LO) for each store to see if it is about to expire. That way, I would keep warning them only until a new date for that store and type is created.

I think I need to add MAX() somewhere and have tried it a few ways, but not quite sure where/how to get this syntactically correct.

Thanks in advance for your guidance!

SOLUTION: Here is what I ended up doing to solve it using a CTE and grouping:

with LATEST as (select RESTORE, REDATETYPE, max(REDATE) as expdate
  from FRESTOR02 group by RESTORE, REDATETYPE)
select LATEST.RESTORE, LATEST.REDATETYPE, LATEST.expdate
  from LATEST
where LATEST.expdate between Current_Date and Current_Date + 2 months
  and LATEST.REDATETYPE in ('LE', 'LO')
order by LATEST.RESTORE

So the CTE first selects just the latest dates for each store and type into a temporary result set called LATEST, and then the main select finds those of type LE or LO that will be expiring in the next two months.

I'm far from being an SQL expert, but making progress (often thanks to all you fine people here on this forum) and this one seems to work.