Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    21

    Unanswered: SQL Query Needed

    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
    Thanks in advance

  2. #2
    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

    3b: not found: you're done. return the start and end date.

  3. #3
    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 10:41.

  4. #4
    Join Date
    Nov 2002
    Posts
    272
    Quote 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.

    Quote 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. #5
    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. #6
    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
  •