How to Design Proper Query to Retrieve Records by Date

To demonstrate decisions when designing a query, we will use a small sample business use case.

Sample Business Use Case

For calculation of the quote product prices, you need to use data about product cost. Product cost data are stored in a Product Extension named ProductCost (see details below). To select the correct cost for calculation, you are asked to use a record which is "valid" on the day when the quote starts to be valid, i.e. on the quote’s effective date.

Create Helper Drawing

Quick drawing will help you a lot to design the query properly.

Figure 1. Validity of the quote and cost records shown at the timeline

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.

Decide Based on Drawing

In our sample, the business person decided to use the record #2 (blue). Note that it is quite common because in pricing, we often want to use data which are "valid" at the moment when the quote starts to be valid.

But in your project you can have different needs, and it always depends on the actual business requirements.

Design the Query

Based on the business decision above we know that we should consider only the product cost records whose validity date range:

starts at or before the quote’s effective date;
ends at or after the quote’s effective date.

But because we have only information about start of the validity date range, we must think about the conditions for data retrieval differently, and consider only the record:

whose validity starts at or before the quote’s effective date;
whose ValidFrom is the closest (from the left side when on the timeline) to the quote’s effective date. In other words, it has the biggest ValidFrom from all those records whose ValidFrom starts before the effective date.

The first condition can be implemented as a Filter and the second one can be done using sorting.

Query solving the sample business use case requirement

return api.find("PX",
        0,
        1,                                               //❶
        "-attribute1",                                   //❷
        ["attribute2"],                                  //❸
        Filter.equal("name", "ProductCost"),             //❹
        Filter.equal("sku", api.product("sku")),
        Filter.lessOrEqual("attribute1",
                 api.targetDate().format("yyyy-MM-dd")), //❺
)?.getAt(0)?.attribute2 as BigDecimal                    //❻

❶ We limit the number of records retrieved because thanks to the filtering and sorting we are certain that the 1st record will be the one we need.
❷ We do descending sort by the validFrom field, to ensure that the record with the biggest ValidFrom will appear as the first one in the result set.
❸ We only need to retrieve a value from the cost field.
❹ Remember that in case of Product Extension tables, we also have to filter by the name of the extension table.
❺ We take only records whose validFrom date starts at or before the quote’s effective date. Remember that in the Quotes module, the logic can get the quote’s effective date by calling api.targetDate(). Additionally, the date filters require the date value to be converted to ISO 8601 date format "yyyy-MM-dd".
❻ Because we limited the query to return maximum one record, we read the 1st record (with index 0) from the returned list and then from that record we take only the value of the cost field. Lastly, because we retrieve a value from the "attribute#" column (and in the query we limited the list of fields returned), the value returned back will be be of the String type, so we have to make sure it is converted to proper data type.

Product Cost Table Description

Our sample business use case expects the product cost data in the following format where each record/row represents the cost of the product during certain date range (commonly called validity).

Product ID (SKU)	Valid From (attribute1)	Average Cost (attribute2)	Currency (attribute4)

Product ID (SKU)	Valid From (attribute1)	Average Cost (attribute2)	Currency (attribute4)
MB-0001	2021-11-01	10.3	USD
MB-0001	2021-12-01	10.5	USD
MB-0001	2022-01-01	12.1	USD
MB-0001	2022-03-01	11.9	USD

When the validity period is not defined fully using both Valid From and Valid To, then in pricing scenario this usually means that the cost value in:

The 1st row is valid from 2021-11-01 to 2021-11-30.
The 2nd row is valid from 2021-12-01 to 2021-12-31.
The 3rd row is valid from 2022-01-01 to 2022-02-28.
The 4th row is valid from 2022-03-01 to "end of time".