I have a table that stores records each with two date fields - an Effective (start) date and Inactive (end) date.
How do I retrieve a record for a specific date e.g. with a record that has a Start date of 01/03/2023 and an End date of 10/04/2023 how do I retrieve the record for 23/03/2023?
Also how would I retrieve multiple records for a range of dates e.g. a record for each day between 06/03/2023 and 05/04/2023? I can generate a table with the required date range but can't figure out how to join or use it to run a query that will produce a record for each date in the range.
Thanks