But here the member has a broken eligibility in this case we need to
take only his latest record i.e.
EFFECTIVE_DATE JAN-01-2005 and TERM_DATE OCT-31-2005. But there
is one more condition that is applicable to this situation. You need
to consider the number of days for which the member doesnt have
eligibility. Let us take it as 30 days.
If the number of days is < =30 then we have to consider as if he has
continuous eligibility. Then we have to follow the first scenario.
If the number of days is > 30 then he has a broken eligibility then
we have to go for the second scenario. Please note that the number
30 I mentioned as example. You can store it in a variable and I
could confirm the number of days tomorrow.
plz help in sorting out this problem
It is to be in plsql
Thanks in advance
CREATE OR REPLACE PROCEDURE YourProcedureName
(p_Subscriber_ID IN NUMBER,
p_Threshold IN NUMBER, -- number of 'unbroken' days allowed while still having unbroken eligibility
p_Effective_Date OUT DATE,
p_Term_Date OUT DATE) AS
-- variable declaration
-- cursor for finding a valid earlier record
CURSOR c_Valid_Record (a_Sub IN NUMBER, a_Threshold IN NUMBER, a_Date IN DATE) IS
SELECT max(effective_date) -- max() in case multiple valid records are present
WHERE SUBSCRIBER_ID = a_Sub
AND a_Date - TERM_DATE <= a_Threshold;
-- get dates for record with highest TERM_DATE for this subscriber
SELECT a.EFFECTIVE_DATE, a.TERM_DATE
INTO v_Effective_Date, v_Term_Date
FROM YourTable a
WHERE SUBSCRIBER_ID = p_Subscriber_ID
AND TERM_DATE = (SELECT max(TERM_DATE)
FROM YourTable b
WHERE b.SUBSCRIBER_ID = p_Subscriber_ID);
-- Get Effective_Dtae from previous period
OPEN c_Valid_Record(p_Subscriber_ID, p_Threshold, v_Effective_Date);
FETCH c_Valid_Record INTO v_New_Eff_Date;
-- Exit the loop when no valid previous period is found
EXIT WHEN c_Valid_Record%NOTFOUND;
-- Set Effective Date equal to the date that was retrieved
v_Effective_Date := v_New_Eff_Date;
-- Close the cursor if it's still open
IF c_Valid_Record%ISOPEN THEN
p_Effective_Date := v_Effective_Date;
p_Term_Date := v_Term_Date;
You need to fill in some information yourself, like the procedure name and table name. I made some assumptions about data types; check them.
Also, I assumed there is at least 1 record present for the incoming subscriber_id, and no overlapping periods are in the table for a subscriber.
As I said, it's not guaranteed to work, since I can't test it. There may be typo's and I'm not entirely sure about the Loop / Exit When / End loop construction.