I have a report which is based on a join.
Query A - has data items related to Product
Query B - had data items related to a Contract
Query C is a join of Query A and Query B with following cardinality(using a primary key)
Each A has one and only one B, B has one or more A.
All the parameters are applied on the child queries i.e, queries A and B.
Query C has a filter to return records with Max End Date (Contract)
This logic works for majority of the situations except a few. The exception being
B has contracts which has a start date and end date. For certain products, the contracts expire every year. If the user runs the report for a Particular product number without using a date parameter, the result set is as expected i.e a record with a max end date (ex: end date = Mar 2019)
However, when the user runs the report for the same product number but with a date prompt for 2018, the report returns a record for the product which has a contract expiring in 2018. While technically true based on the data, the user is now requesting that the filter for max date should be applied first and then the parameter for the date value be passed so that result set is null.
I have tried to use except function but the result is not as expected.
can you share your syntax here?