Limit Join By Date

I have a query that is the initial part of a stored procedure; the results are put in a temporary table that will be used by several other operations. I'm trying to consolidate some of these, possibly getting the whole thing down to one query. One of steps I'm trying to consolidate results in a join that looks like this (where c is a table alias created previously):
Code:


LEFT JOIN proccode_t pc ON pc.proccode=c.proccode
AND (pc.effectivedate<=c.dateofservice OR pc.effectivedate IS NULL)



I don't have any influence over the schema in this database, I can only write select queries. Things are set up such that proccode_t will have several records for any given proccode, each with a different effectivedate. The oldest/original effectivedate is often NULL. This means my join up there often matches up with several records in proccode_t, when I only ever want to match the one most recent and occasionally not match anything at all.

I know a few ways to accomplish this (as covered in another recent thread here), but none of them seem to match well to this situation. A sub query that uses TOP 1 ORDER BY effectivedate DESC would almost cover it, but if the query returns NULL that might or might not mean it matched the NULL effectivedate for that proccode. It's also kinda slow.

The proccoce_t table does contain another field to tell you which of the codes is currently effective aside from just using the date, and that would be the normal way to do this here. However, this procedure is for auditing old data and therefore the currently effective proccode may not be the one I'm concerned with.

Any thoughts?