Select records by most recent effective date

[hyperiest]

I am attempting to write a query to select records from a table that are identical except for the effective date of the records. The table looks somthing like this:

UICDR PARA LIN Auth EffDate
WBTAAA 101 02 3 2003/10/17
WBTAAA 101 02 3 2002/09/18
WBTAAA 101 02 3 2000/10/16
WFYTAA 102 01 2 2002/09/16
WFYTAA 102 01 2 2001/08/28

As you can see, some of the records have EffDate values that have not yet come to pass, and as such, I need to filter out these records. Others have multiple EffDate values that have already passed, and as such I need to filter them out as well. I want to select only those records with the most recent effective date, based on today's date.

Can anyone help me?

Thanks,

Captain_D

 

[rac2]

Well the logic would be to look at the rows with an EffDate before now; among those find the ones with the most recent date for each thing. So-

SELECT UICDR, PARA, LIN, Auth, MAX(EffDate)
FROM MyTable
WHERE EffDate <= now()
GROUP BY UICDR, PARA, LIN, Auth

If indeed it is true that the table has many rows with the same values except for the EffDate then the statement above will yield the same rows as the following; however the following one does not show all of the columns.

SELECT UICDR, MAX(EffDate)
FROM MyTable
WHERE EffDate <= now()
GROUP BY UICDR

Does that work for your requirement?

 

[hyperiest]

This worked to an extent. However, some of the authorizations no longer exist, but they still show up in the query results, as there is no record corresponding to it with a more recent effdate value, but I need to screen out these records as well.

Captain_D

 

[swampBoogie]

What do you mean by

<quote>
some of the authorizations no longer exist
</quote>

?

Should it exist at least two records for the same UICDR, PARA, LIN, Auth with Effdate <= now() ? If so

SELECT UICDR, PARA, LIN, Auth, MAX(EffDate)
FROM MyTable
WHERE EffDate <= now()
GROUP BY UICDR, PARA, LIN, Auth
having count(*) >= 2

 

[hyperiest]

The table lists records pertaining to personnel authorizations in military organizations. The authorizations change annually, so sometimes the authorizations from a precious year do not carry over into the current or future years. If within an organization, e.g., WATBAA there existed a record for 1 authorization of personnel code 88M - grade E7 against PARA 101 LIN 03 by a previous EffDate, per the current EffDate, the authorization may no longer exist (in other words, the person is no longer authorized).

I may have to give up and seek the solution with the personnel who create the table, as I have found other egregious errors in the way they have compiled the data.

I do appreciate your help, though, as it has taught me how to select the data I need when the creators of the table get their data correct.

Thanks again,

Captain_D