Re: SQL Problem

From: D R Patterson <patterd1_at_home.com>
Date: Mon, 01 Oct 2001 15:41:10 GMT
Message-ID: <3BB88D9B.695C10CB@home.com>

Niall, how about the following:

set serveroutput on Format TRUNC;

DECLARE
  v_empno career_history.empno%TYPE;   CURSOR C_multi1 IS
    SELECT empno, count( storeid) storecount     FROM career_history
    HAVING count(storeid) > 1
    GROUP BY empno;
  CURSOR C_multi2 IS

    SELECT      storeid,
                start_period,
                end_period
    FROM        career_history
    WHERE       empno = v_empno

    ORDER BY start_period;
  v_prior_end career_history.end_period%TYPE;   v_prior_store career_history.storeid%TYPE; BEGIN
  DBMS_Output.Put_Line( 'Start of list of problem employees.');

• this gives me a list of the employees who have worked for
• two different stores, or multiple periods in one store. FOR v_empl_multi in C_multi1 LOOP
  • save the employee number v_empno := v_empl_multi.empno;
  • open the second cursor to walk through the entries and
  • see if there is an overlap v_prior_end := -1; FOR v_empl_hist in C_multi2 LOOP
    • look at each empl/store row IF v_prior_end > 0 -- if this not the first entry for this empl AND v_prior_end >= v_empl_hist.start_period THEN
      • starting before left prior store! DBMS_Output.Put_line( 'Overlap with employee ' || To_Char( v_empno)); DBMS_Output.Put_line( ' Worked at store ' || To_Char( v_prior_store ) || ' until period ' || To_Char( v_prior_end ) ); DBMS_Output.Put_line( ' Started at store ' || To_Char( v_empl_hist.storeid ) || ' in period ' || To_Char( v_empl_hist.start_period ) ); END IF;
  • keep values for next entry v_prior_end := v_empl_hist.end_period; v_prior_store := v_empl_hist.storeid; END LOOP;
  • DBMS_Output.Put_Line( v_empl_multi.empno ); END LOOP; END; /

It has two cursors -- the first will select only the employees with multiple rows of experience. The second will look at the career history rows for each identified employee and flag the ones with overlapping work history.

I think it would be fairly efficient if the right indices are present.

Dave Patterson
patterd1_at_home.com Received on Mon Oct 01 2001 - 10:41:10 CDT