Query for validating date spans
Posted on 2011-03-04
Experts need your help...
We have a business requirement to validate a code (master tableA) against claim dates (TRANSACTION TABLE B).
If the code dates are within claim dates we return 'TRUE' else 'FALSE'
The existing query is as follows:
SELECT COUNT ( * ) AS COUNT
WHERE CODE = 'A'
AND from_date <= TO_DATE ('12/07/2010', 'MM/DD/YYYY') -- claim FROM date
AND TO_DATE >= TO_DATE ('01/06/2011', 'MM/DD/YYYY'); -- claim TO date
IF v_cnt > 0 RETURN TRUE ELSE RETURN FALSE
The problem starts here...
We have these records in master table...
TableA (MASTER TABLE)
CODES FROM_DATE TO_DATE
'A' 01/01/2000 12/31/2010
'A' 01/01/2011 12/31/2020
The incoming claim has these records...
TABLEB (claim table ) - Transaction
CLAIMNO FROM_DATE TO_DATE
1 12/12/2010 01/07/2011
So the existing query fails as you can see in TABLEA that we have MULTIPLE spans for codes 'A'
I need help to modify the existing query so that it takes into account for MULTIPLE SPANS and return result appropriately
Let me know if I you need further explaination on this