1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

what is the select statement which solve the problem ,help me?

Discussion in 'SQL PL/SQL' started by amora_amoraus@yahoo.com, Dec 29, 2013.

  1. How are you?
    SQL> select * from tst
    2 order by id,vacdate;


    ID VACDATE VACINTERV
    ---------- --------- ----------
    10 15-JAN-13 4
    10 25-JAN-13 6
    10 27-JAN-13 4
    20 15-JAN-13 4
    40 01-MAY-13 3
    40 13-OCT-13 7
    50 13-OCT-13 7
    50 17-OCT-13 7
    60 24-MAY-13 5
    60 24-MAY-13 3

    this table contain id ==>employee id & vacdate ==> is the start date of vacation of the employees && vacinterv ===> the time interval of the vacation for example 7 means 7 days from 13-oct-13 until 20-oct-13

    the problem is there are employees (i.e id 10) take overlapping vacation [take 25-jan-13 (interval 6 ) until 21-jan-13] but by mistake take 27-jan-13 other vacation which cause vacation overlapping
    How can i select the employees who have overlapping vacation?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    simple example :
    Code (SQL):

    WITH
    emp_vacan AS (
    SELECT 10 ID,to_date('15-JAN-13','DD-MON-RR') VACDATE ,4 VACINTERV FROM dual UNION ALL
    SELECT 10 ID,to_date('25-JAN-13','DD-MON-RR') VACDATE ,6 VACINTERV FROM dual UNION ALL
    SELECT 10 ID,to_date('27-JAN-13','DD-MON-RR') VACDATE ,4 VACINTERV FROM dual UNION ALL
    SELECT 20 ID,to_date('15-JAN-13','DD-MON-RR') VACDATE ,4 VACINTERV FROM dual UNION ALL
    SELECT 40 ID,to_date('01-MAY-13','DD-MON-RR') VACDATE ,3 VACINTERV FROM dual UNION ALL
    SELECT 40 ID,to_date('13-OCT-13','DD-MON-RR') VACDATE ,7 VACINTERV FROM dual UNION ALL
    SELECT 50 ID,to_date('13-OCT-13','DD-MON-RR') VACDATE ,7 VACINTERV FROM dual UNION ALL
    SELECT 50 ID,to_date('17-OCT-13','DD-MON-RR') VACDATE ,7 VACINTERV FROM dual UNION ALL
    SELECT 60 ID,to_date('24-MAY-13','DD-MON-RR') VACDATE ,5 VACINTERV FROM dual UNION ALL
    SELECT 60 ID,to_date('24-MAY-13','DD-MON-RR') VACDATE ,3 VACINTERV FROM dual
    )

    ,RESULT AS
    (
    SELECT
      e.*,
      lag(e.vacdate) OVER (partition BY e.id ORDER BY e.vacdate) prev_vacdate,
      lag(e.vacinterv) OVER (partition BY e.id ORDER BY e.vacdate) prev_vacinterv
       
    FROM emp_vacan e
    )

    SELECT
        r.*
    FROM RESULT r
    WHERE
    (r.prev_vacdate+r.prev_vacinterv) BETWEEN  r.vacdate AND r.vacdate+r.vacinterv

    SQL>
            ID VACDATE    VACINTERV PREV_VACDATE PREV_VACINTERV
    ---------- --------- ---------- ------------ --------------
            10 27-JAN-13          4 25-JAN-13                 6
            50 17-OCT-13          7 13-OCT-13                 7



     

    link :
    http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174
    http://www.oracle-base.com/articles/misc/lag-lead-analytic-functions.php