2 Replies Latest reply: Nov 12, 2012 11:03 AM by bsc7080oec RSS

    SQL Experts - Need result to show dates when a manager had direct reports

    bsc7080oec
      Does anyone have SQL lying around that can merge a table of dates into a reduced data set that represents a blend of the dates ? See below.

      CREATE dummydates_tbl
      (EMPID NUMBER,MANAGERID NUMBER,STARTDATE DATE,ENDDATE DATE);

      INSERT INTO dummydates_tbl (9769,1234,to_date('01-JAN-2012','DD-MON-YYYY'),to_date('30-JAN-2012','DD-MON-YYYY'));
      INSERT INTO dummydates_tbl (5512,1234,to_date('25-JAN-2012','DD-MON-YYYY'),to_date('25-FEB-2012','DD-MON-YYYY'));
      INSERT INTO dummydates_tbl (9769,1234,to_date('10-MAR-2012','DD-MON-YYYY'),to_date('30-JUN-2012','DD-MON-YYYY'));
      INSERT INTO dummydates_tbl (8853,1234,to_date('10-MAR-2012','DD-MON-YYYY'),to_date('30-JUN-2012','DD-MON-YYYY'));

      COMMIT;


      /*

      Need SQL that produces a result that shows dates that a manager had employees reporting to him/her

      MANAGERID STARTDATE ENDDATE
      --------- --------- -------
      1234 01-JAN-2012 25-FEB-2012
      1234 10-MAR-2012 30-JUN-2012

      Data Picture :

      |--------------| |-------------|
      |---------|
      |-------------|

      */

      Edited by: bsc7080oec on Nov 12, 2012 11:34 AM