Forum Discussion
Select MIN of 4 Dates
- George_HepworthMay 17, 2022Silver Contributor
You should have a table with one record for each type of date and a foreign key to the parent LCID.
The table, therefore, has four fields, including its own Primary Key.
PrimaryKey
LCID (Foreign key to the parent table)
DateType or DateSequence (NoticeDate, Expirey1Date, Expiry2Date, UltExpireydate)
SequenceDate
Now, a query against this table, using Min or Max for SequenceDate, returns the earliest or latest date for each LCID.
SELECT LCID, Min(SequenceDate) as EarliestDate
FROM tblofExpireyDates
GROUP BY LCID
or
SELECT LCID, Max(SequenceDate) as LastestDate
FROM tblofExpireyDates
GROUP BY LCID
That would have to be joined back to the parent same table to get the appropriate DateSequence that corresponds to that LCID and date.
SELECT LCID, EarliestDate, DateSequenceFROM tblofExpireyDates INNER JOIN qryofEarliestDates
On tblofExpireyDates.LCID = qryofEarliestDates.LCID and tblofExpireyDates.SequenceDate = qryofEarliestDates.EarliestDate
or
SELECT LCID, LastestDate, DateSequence
FROM tblofExpireyDates INNER JOIN qryofLatestDates
On tblofExpireyDates.LCID = qryofLatestDates.LCID and tblofExpireyDates.SequenceDate = qryofLatestDates.EarliestDate