Return date when found a break in pl/sql

Question

i have a table with three columns

Position_id  start_date  end_date
      10       01-JUN-05    31-DEC-2012
      10       01-JAN-13    31-DEC-4712

now i want to check that when start_date is 1 date after the end_date then i should continue with the loop. like in the above case start_date '01-jan-2013' is 1 date after 31-dec-2012. so it should return the start date '01-jun-2005'

But as soon as there is an exception like :-

Position_id  start_date  end_date
      10       01-JUN-05    31-DEC-2012
      10       01-FEB-13    31-DEC-4712

Like in this case,the start_date is not 1 date after the end_date 31-dec-2012. so it should return the start date '01-feb-2013'

For this i have made a function.. but i am not getting how to use and return the date.

create or replace 
FUNCTION XX_test_RATING_FUNC(P_PERSON_ID NUMBER)
RETURN VARCHAR2
AS
V_PERSON_ID VARCHAR2(100);
V_POS VARCHAR2(110);
V_RATE VARCHAR2(100);
V_POS_DT DATE;

/**Cursor to fetch date when a position was attached with an employee **/

CURSOR CUR_POS_st_date
is

    SELECT (EFFECTIVE_START_DATE),to_char(effective_end_date,'DD-MON-YYYY')
    FROM tablePAAF
    order by desc;



CURSOR CUR_POS
IS

    SELECT COUNT(DISTINCT position_id ) from table;


BEGIN



OPEN CUR_POS_ST_DATE;
FETCH CUR_POS_st_date INTO V_POS_dt;
CLOSE CUR_POS_ST_DATE;


OPEN CUR_POS_ST_DATE;
FETCH CUR_POS_ST_DATE INTO V_PERSON_ID;
CLOSE CUR_POS_ST_DATE;


V_DT DATE ;

DBMS_OUTPUT.PUT_LINE(V_POS_DT||'V_PERSON_ID');
FOR I IN 1..V_PERSON_ID
LOOP
V_DT := I.EFFECTIVE_START_DATE;

 if v_dt=i.effective_end_date
      then

//confusion about this part//

else
 Break
--        END;
      EXCEPTION WHEN OTHERS 
      THEN
      DBMS_OUTPUT.PUT_LINE('Error'||SQLERRM);
       RETURN 'ERROR';
END XX_test_RATING_FUNC;

I need help only for the for loop part. dont know how to fiddle with the dates

Answer 1

Are you in a position to use window functions? Taking your second example and making the Position_id 11 for the purposes of this SQL Fiddle, such a thing can solve this in a set-based way ( edit: probably better to say "non-iterative way" ) without getting into the need for a function ( or cursor ).

Recursively traversing your table by selecting any records which exist with a start date equal to one day after an end date, you can then take the maximum "rank" for each Position_id as your final answer.

;
WITH cte ( RID, Position_id, start_date, end_date ) AS (
    SELECT  ROW_NUMBER() OVER (
                PARTITION BY Position_id
                ORDER BY start_date )           RID,
            Position_id, start_date, end_date
    FROM    tablePAAF
    UNION ALL
    SELECT  b.RID + 1, a.Position_id, a.start_date, b.end_date
    FROM    tablePAAF a
    INNER JOIN cte b
        ON  a.Position_id = b.Position_id
        AND a.end_date = b.start_date - INTERVAL '1' DAY )
SELECT  p.Position_id, p.start_date, p.end_date
FROM (  SELECT  Position_id, MAX( RID )         RID    
        FROM    cte
        GROUP BY Position_id ) mpr
LEFT JOIN cte p
    ON  mpr.Position_id = p.Position_id
    AND mpr.RID = p.RID
ORDER BY p.Position_id;

Breaking what is happening here down, we will look at the first part of the Common Table Expression ( CTE, you can think of it like an ad-hoc view, really ) or as it's known in Oracle lingo, recursive subquery factoring. In this SQL Fiddle, we see how the window function, ROW_NUMBER() gives each row a "rank," starting at one for each Position_id ( our PARTITION ) and increments that "rank" for each subsequent start_date ( our ORDER BY ).

;
WITH cte ( RID, Position_id, start_date, end_date ) AS (
    SELECT  ROW_NUMBER() OVER (
                PARTITION BY Position_id
                ORDER BY start_date )           RID,
            Position_id, start_date, end_date
    FROM    tablePAAF )
SELECT  *
FROM    cte;

Resulting in:

╔═════╦═════════════╦═════════════════════════════════╦═════════════════════════════════╗
║ RID ║ POSITION_ID ║           START_DATE            ║             END_DATE            ║
╠═════╬═════════════╬═════════════════════════════════╬═════════════════════════════════╣
║  1  ║      10     ║ June, 01 2005 00:00:00+0000     ║ December, 31 2012 00:00:00+0000 ║
║  2  ║      10     ║ January, 01 2013 00:00:00+0000  ║ December, 31 4712 00:00:00+0000 ║
║  1  ║      11     ║ June, 01 2005 00:00:00+0000     ║ December, 31 2012 00:00:00+0000 ║
║  2  ║      11     ║ February, 01 2013 00:00:00+0000 ║ December, 31 2012 00:00:00+0000 ║
╚═════╩═════════════╩═════════════════════════════════╩═════════════════════════════════╝

From here, we add the recursive part of our CTE. This additional SELECT takes the data in tablePAAF and joins it to our newly-created CTE on the condition that for a particular Position_id, the end_date of a record is equal to one day less than the start_date of a record in the CTE, creating a sort of "back in time" recursion. To do this date math, we use the INTERVAL keyword with '1' DAY to let Oracle know we would like to subtract a single day for the comparison. Because we are interested in creating records which take the first start date and last end date, we choose to persist the value of the joined CTE record's end_date and take the new start_date from the original data ( Position_id could be either ). This new record's "rank" is then incremented by one. Only one record like this is found, so we get the following results:

╔═════╦═════════════╦═════════════════════════════════╦═════════════════════════════════╗
║ RID ║ POSITION_ID ║           START_DATE            ║             END_DATE            ║
╠═════╬═════════════╬═════════════════════════════════╬═════════════════════════════════╣
║  1  ║      10     ║ June, 01 2005 00:00:00+0000     ║ December, 31 2012 00:00:00+0000 ║
║  2  ║      10     ║ January, 01 2013 00:00:00+0000  ║ December, 31 4712 00:00:00+0000 ║
║  1  ║      11     ║ June, 01 2005 00:00:00+0000     ║ December, 31 2012 00:00:00+0000 ║
║  2  ║      11     ║ February, 01 2013 00:00:00+0000 ║ December, 31 2012 00:00:00+0000 ║
║  3  ║      10     ║ June, 01 2005 00:00:00+0000     ║ December, 31 4712 00:00:00+0000 ║
╚═════╩═════════════╩═════════════════════════════════╩═════════════════════════════════╝

With this result set, all we need to do is get the maximum "rank" for each Position_id, bringing us back to the original SQL Fiddle.