...
Create Helper Drawing
Quick drawing will help you a lot to design the query properly.
In cooperation with a pricing manager, we recommend that you make a drawing like this every time you need to design a query based on a date:
Place there the boxes representing the validities (date ranges) of the data records you will be retrieving. Note that the records may have also information about "ValidTo". If they do, draw them in your diagram too.
Place there a box representing validity of the document (e.g. quote).
Based on the business requirement, find out which of the dates (decision date) you will use to find the proper record. In our case, we were asked to use the first day of quote validity (called Effective Date).
It is highly recommended to place there also a "today mark", to emphasize that the day of decision making (i.e. when you run the query) is usually not the same as the "today" date. Typically you will calculate "future" quotes.
With such a drawing, the business person needs to decide which of the records should be used in the given situation.
...