SQL help - date ranges

[Dr.K]

Ok, so I have a table that tracks 401K contribution rates. Its set up as a 'profile' table; If you pull all the records for a specific participant, each row has:

-Data Field (the percentage contribution)
-Source Code
-Effective Date
-Expiration Date

If the record is currently active, the Expiration date is Null.

Ok, what I need, is a list of all relevant Source Codes, during a specific time period (between datFirst and datLast). The rest of the SQL is complicated, but it works, so here is the relevant part of the WHERE clause:

[vba]& " AND ( " _
& " ( TABLE.EXPIRATION_DATE <= 'datLast'" _
& " AND TABLE.EFFECTIVE_DATE >= 'datFirst' )" _
& " OR" _
& " ( ( TABLE.EXPIRATION_DATE >= 'datLast'" _
& " OR TABLE.EXPIRATION_DATE IS NULL ) " _
& " AND TABLE.EFFECTIVE_DATE < 'datLast' )" _
& " )" _[/vba]

Query edited for clarity. Yes, I know that you can't put a variable directly into a literal date string, I just removed the text formatting to make it easier to read)

Does this capture EVERY code that was valid between datFirst and datLast, or am I missing something?

[stanl]

I did this quickly, maybe need glasses or another drink, but aren't you basically saying

TABLE.EFFECTIVE_DATE >= 'datFirst' AND TABLE.EFFECTIVE_DATE < 'datLast'

or possibly TABLE.EFFECTIVE_DATE BETWEEN ('datFirst','datLast')


WHERE ( TABLE.EXPIRATION_DATE <= 'datLast' OR TABLE.EXPIRATION_DATE IS NULL )

[Dr.K]

Stanl- thanks for the help, but I think I figured this out exhaustively.

I built a matrix to track all the possibilites, and figured out logic for each scenario... and then I realized that becuase Effective Date is always < Expiration date, the logic collaspes down to this:

[vba]& " AND TABLE.EFFECTIVE_DATE < 'datLast'" _
& " AND ( TABLE.EXPIRATION_DATE > 'datFirst'" _
& " OR TABLE.EXPIRATION_DATE IS NULL )"[/vba]

In other words:
If the Effective Date is before the LastDate, then the code is valid if the Expiration Date is after the FirstDate, or there is no Expiration Date.

A bit simpler, eh?
If there is still a hole in my logic, please point it out.

[stanl]

Yes, unless you are using TimeStamps.