I need to create a SQL to retrieve some value. The SQL has many joins, but there is a date comparison to do also.
In this system, an child-statement belongs to a master-statement.
However, the master-statement has a date range.
The child-statement also has a date range.
So, we need to get information for the child-statement which belongs to the date-range of the master-statement.
How can we create the WHERE-CLAUSE because there can be about 4 or 5 situations.
Is it good practice to do something like this:
SELECT *
FROM tables
WHERE
conditons AND
date_range_check(ms.start_date, ms.end_date, cs.start_date, cs_end_date) = 'TRUE';
DATE_RANGE_CHECK is a function which returns a TRUE or FALSE value and is defined in the same SP in the declare section.
Will this have a performance impact?
Also, how do check the dates since there could be many possibilities?
Like
ms.start_date <--------------------------------------------------------------->ms.end_date
(1.) cs.start_date<-------->cs.end_date
(2.) cs.start_date<------------------------------------------>cs.end_date
(3.) cs.start_date <--------------------------------------------->cs.end_date
(4.) cs.start_date<---------------------------------------------------------------------------------------------------------------------->cs.end_date