1. Registered User
Join Date
Oct 2005
Posts
21

SCENARIO 1

SUBSCRIBER_ID EFFECTIVE_DATE TERM_DATE

1000 OCT-31-2004 DEC-31-2004
1000 JAN-01-2005 OCT-31-2005

Here in the above scenario the member has continues eligibility. So
here we have to take the effective date OCT-31-2004 and TERM_DATE
OCT-31-2005

SCENARIO 2

SUBSCRIBER_ID EFFECTIVE_DATE TERM_DATE

1000 OCT-31-2004 DEC-01-2004
1000 JAN-01-2005 OCT-31-2005

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 doesnt 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

2. Registered User
Join Date
Nov 2002
Posts
272
Do you need help with the program logic or the actual programming of the stored procedure?

The logic would be something like:

1: store the start and end date from the record with the highest end date in variables

2: look for another record with end date less than 30 days before the stored start date

3a: if found, then your new start date value is the start date from the record you found. go to step 2

3. Registered User
Join Date
Oct 2005
Posts
21
the actual programming of the stored procedure is needed
There can be more than 2 rows for each subscriber id(n nos)
Last edited by Nura; 11-01-05 at 09:41.

4. Registered User
Join Date
Nov 2002
Posts
272
Originally Posted by Nura
the actual programming of the stored procedure is needed
I don't have access to an Oracle instance here. I'm working in a SQL Server environment now
I could whip something up in notepad, but there would be compile errors for sure.

Maybe some of the other Oracle geniuses will help you writing this.

Originally Posted by Nura
There can be more than 2 rows for each subscriber id(n nos)
My logic takes that into account. step 3a takes you back to step 2 to look for another record. It only exits (through step 3b) when no more records are found.

I wonder: why do you want to do this in pl/sql if you don't know the language?

5. Registered User
Join Date
Nov 2002
Posts
272
Oh heck, here goes:

Code:
```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
v_Effective_Date DATE;
v_Term_Date      DATE;
v_New_Eff_Date   DATE;

-- 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
FROM YourTable
WHERE SUBSCRIBER_ID = a_Sub
AND a_Date - TERM_DATE <= a_Threshold;

BEGIN

-- 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);

LOOP
-- 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;
CLOSE c_Valid_Record;
-- Set Effective Date equal to the date that was retrieved
v_Effective_Date := v_New_Eff_Date;
END LOOP;

-- Close the cursor if it's still open
IF c_Valid_Record%ISOPEN THEN
CLOSE c_Valid_Record;
END IF;

p_Effective_Date := v_Effective_Date;
p_Term_Date := v_Term_Date;
END;```
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.

6. Registered User
Join Date
Oct 2005
Posts
21

SQL Problem

I have worked on the procedure and made chnges to it to suite the oracle method.It worked.Thank you so much

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•