How could I leverage this to get, say, the "most current" version of a contract (i.e., either the contract currently in effect or the latest contract even if it was expired) *and* the latest version of the contract (i.e., it doesn't take effect until the future? I have a query using subqueries for this that looks something like this (this is simplified a bit). The row_number() query is much more concise but only returns that max contract date for each contract, which leaves out the current dates for those that have them:
SELECT DISTINCT group_number
FROM contracts curr1
AND GETDATE() BETWEEN contract_from_date AND contract_thru_date
FROM contracts prev
) contract_thru_date FROM contracts curr
EDIT: NM, I figured it out. All I had to was add an or statement to the where in the CTE. Great article, came at a good time!