This discussion is archived
11 Replies Latest reply: Oct 11, 2012 11:40 PM by 9876564 RSS

Need a help

9876564 Newbie
Currently Being Moderated
HI All,
I have below situation where i need to fetch the hire date for an employee

Table name : Emp
Emp No | Hire date | Dept | date From | Date To
---------------------------------------------------------------------------------------
123 | 'NULL ' |A| 12/12/2001 | 12/12/2004
123 | 'NULL ' |B| 12/12/2004 | 12/12/2007
123 | 12/12/2001 |C| 12/12/2007| 12/12/2008
123 | 12/12/2002 |D| 12/12/2008| 12/12/2009

I need to find out the Hire date and Dept for an employee on 12/12/2002 ( This date should be between "Date from" and "Date to" , in case hire date is null then the first available data should come).
In the above example the hire date should be 12/12/2001 and the dept should be "A".

Considering there is a huge amount of data in my table ( i do not want to hit my table twice ), kindly help me in getting the optimum query.


Thanks in Advance

Edited by: AbSHeik on Oct 11, 2012 2:53 AM

Edited by: AbSHeik on Oct 11, 2012 4:34 AM
  • 1. Re: Need a help
    jeneesh Guru
    Currently Being Moderated
    Check for FIRST_VALUE analytic function
  • 2. Re: Need a help
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    AbSHeik wrote:
    ... I need to find out the Hire date and Dept for an employee on 12/12/2002 ( This date should be between "Date from" and "Date to" , in case hire date is null then the first available data should come).
    One way to code that is
    ...
    WHERE   DATE '2002-12-12'    -- or whatever target date you want
            BETWEEN date_from
            AND     date_to
    Rows where date_from is NULL will not be returned.

     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 3. Re: Need a help
    Nag Aswadhati Explorer
    Currently Being Moderated
    try this

    select first_value(hiredate) over (order by hiredate) from emp
    where to_date('12/12/2002','DD/MM/YYYY')
    BETWEEN TO_DATE(datefrom,'DD/MM/YYYY') AND TO_DATE(dateto,'DD/MM/YYYY')
  • 4. Re: Need a help
    9876564 Newbie
    Currently Being Moderated
    Nag Aswadhati wrote:
    try this

    select first_value(hiredate) over (order by hiredate) from emp
    where to_date('12/12/2002','DD/MM/YYYY')
    BETWEEN TO_DATE(datefrom,'DD/MM/YYYY') AND TO_DATE(dateto,'DD/MM/YYYY')
    It is not working , the hire date is coming as NULL only.
  • 5. Re: Need a help
    9876564 Newbie
    Currently Being Moderated
    AbSHeik wrote:
    HI All,
    I have below situation where i need to fetch the hire date for an employee

    Table name : Emp
    Emp No | Hire date | Dept | date From | Date To
    ---------------------------------------------------------------------------------------
    123 | 'NULL ' |A| 12/12/2001 | 12/12/2004
    123 | 'NULL ' |B| 12/12/2004 | 12/12/2007
    123 | 12/12/2001 |C| 12/12/2007| 12/12/2008
    123 | 12/12/2002 |D| 12/12/2008| 12/12/2009

    I need to find out the Hire date and Dept for an employee on 12/12/2002 ( This date should be between "Date from" and "Date to" , in case hire date is null then the first available data should come).
    In the above example the hire date should be 12/12/2001 and the dept should be "A".

    Considering there is a huge amount of data in my table ( i do not want to hit my table twice ), kindly help me in getting the optimum query.


    Thanks in Advance

    Edited by: AbSHeik on Oct 11, 2012 2:53 AM

    Edited by: AbSHeik on Oct 11, 2012 4:34 AM
    what i have written is

    WITH k AS
    (SELECT * FROM (
    SELECT hire_date,
    ROW_number() over (PARTITION BY emp_no ORDER BY date_from) ron
    FROM (
    SELECT emp_no,Dept_no,hire_date,date_from,date_to
    FROM EMP
    WHERE HIRE_DATE IS NOT null))
    WHERE ron =1)
    SELECT dept_no , nvl(abhi1.hire_date,k.hire_date)
    FROM EMP,k
    WHERE ( '12-Sep-2002'
    BETWEEN date_from AND date_to) OR k.hire_date IS NULL

    Kindly somebody can tell me if this is the best way to write this query or somthing else can be done (table EMP is huge)
  • 6. Re: Need a help
    6363 Guru
    Currently Being Moderated
    Please read the FAQ

    {thread:id=2174552}

    {message:id=9360002}
    {message:id=9360003}
  • 7. Re: Need a help
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi AbSheik,

    I think we should understand better your requirement.

    In the example you posted you have several records of the same employee.

    You said:
    I need to find out the Hire date and Dept for an employee on 12/12/2002
    and this one can be done by:
     WHERE TO_DATE('12-Sep-2002', 'DD-Mon-YYYY') BETWEEN date_from AND date_to
    {code}
    
    In this case only the first record:
    
    {code}
    Emp No | Hire date | Dept | date From | Date To
    123 | 'NULL ' |A| 12/12/2001 | 12/12/2004
    {code}
     meet the requirement. so I guess we take dept 'A'.
    
    I don't understand however what you mean with 
    
    in case hire date is null then the first available data should come
    What do you mean with first available data should come? Does it mean that if the hire date is NULL we have to take it from other rows which are not matching the where conditions? How? a) Shall we take the MIN(hiredate) where empno=123 in the example below? b) Shall we take the first hiredate NOT NULL ordering the records of the same employee by datefrom? Please explain. Regards. Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 8. Re: Need a help
    9876564 Newbie
    Currently Being Moderated
    Alberto Faenza wrote:
    Hi AbSheik,

    I think we should understand better your requirement.

    In the example you posted you have several records of the same employee.

    You said:
    I need to find out the Hire date and Dept for an employee on 12/12/2002
    and this one can be done by:
    WHERE TO_DATE('12-Sep-2002', 'DD-Mon-YYYY') BETWEEN date_from AND date_to
    {code}
    
    In this case only the first record:
    
    {code}
    Emp No | Hire date | Dept | date From | Date To
    123 | 'NULL ' |A| 12/12/2001 | 12/12/2004
    {code}
    meet the requirement. so I guess we take dept 'A'.
    
    I don't understand however what you mean with 
    
    in case hire date is null then the first available data should come
    What do you mean with first available data should come? Does it mean that if the hire date is NULL we have to take it from other rows which are not matching the where conditions? How? a) Shall we take the MIN(hiredate) where empno=123 in the example below? b) Shall we take the first hiredate NOT NULL ordering the records of the same employee by datefrom? Please explain. Regards. Al
    HI ,

    Thanks very much for taking time out to understand my problem.

    What i am asking , if there is no hire_date for the date specified then the query should pick the hire date from the row with the earliest "date_from" where hire date is not null .
    in the above example , the 3rd record is having the earliest 'Date_from' date where hire date is not null , so the hire date from that row should be fetched.

    let me put it in this way

    in the above example,there are 4 values for the column "date_from"
    12/12/2004
    12/12/2007
    12/12/2008
    12/12/2009

    and as we can see for the first two dates we have Hire date as null , which means the earliest avialable date is 12/12/2008 where there is some value for hire date so this value should be fetched.

    I hope i have made my doubt clear.

    Thanks Again for your valuble time.

    Edited by: AbSHeik on Oct 11, 2012 6:12 AM
  • 9. Re: Need a help
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi AbSheik,

    what about something like this:
    SELECT empno, dept
         , NVL(hiredate, (SELECT hiredate 
                            FROM emp e2  
                           WHERE e2.hiredate IS NOT NULL
                             AND e1.empno = e2.empno
                             AND e2.datefrom = (SELECT MIN(datefrom) 
                                                  FROM emp e3
                                                 WHERE e3.empno = e2.empno
                                                   AND e3.hiredate IS NOT NULL
                                                )
                          )
               ) AS hiredate  
      FROM emp e1
     WHERE TO_DATE('12-Sep-2002', 'DD-Mon-YYYY') BETWEEN datefrom AND dateto;
    {code}
    
    i.e.:
    
    {code:sql}
    with emp AS
    (
    SELECT 123 empno, NULL                                hiredate , 'A' dept, to_date('12/12/2001', 'DD/MM/YYYY') datefrom, to_date('12/12/2004', 'DD/MM/YYYY') dateto FROM DUAL UNION ALL
    SELECT 123 empno, NULL                                hiredate , 'B' dept, to_date('12/12/2004', 'DD/MM/YYYY') datefrom, to_date('12/12/2007', 'DD/MM/YYYY') dateto FROM DUAL UNION ALL
    SELECT 123 empno, to_date('12/12/2001', 'DD/MM/YYYY') hiredate , 'C' dept, to_date('12/12/2007', 'DD/MM/YYYY') datefrom, to_date('12/12/2008', 'DD/MM/YYYY') dateto FROM DUAL UNION ALL
    SELECT 123 empno, to_date('12/12/2002', 'DD/MM/YYYY') hiredate , 'D' dept, to_date('12/12/2008', 'DD/MM/YYYY') datefrom, to_date('12/12/2009', 'DD/MM/YYYY') dateto FROM DUAL
    )
    SELECT empno, dept
         , NVL(hiredate, (SELECT hiredate 
                            FROM emp e2  
                           WHERE e2.hiredate IS NOT NULL
                             AND e1.empno = e2.empno
                             AND e2.datefrom = (SELECT MIN(datefrom) 
                                                  FROM emp e3
                                                 WHERE e3.empno = e2.empno
                                                   AND e3.hiredate IS NOT NULL
                                                )
                          )
               ) AS hiredate  
      FROM emp e1
     WHERE TO_DATE('12-Sep-2002', 'DD-Mon-YYYY') BETWEEN datefrom AND dateto;
    
         EMPNO DEPT HIREDATE 
    ---------- ---- ---------
           123 A    12-DEC-01
    1 row selected.
    {code}
    
    Is it possible that you have 2 rows having same datefrom for the same empno? 
    In this case which one will you take?
    
    Or that you don't find any row with hiredate NOT NULL?
    
    Regards.
    Al
    
    Edited by: Alberto Faenza on Oct 11, 2012 3:17 PM
    Added some clarification                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 10. Re: Need a help
    9876564 Newbie
    Currently Being Moderated
    Thank you very much Alberto for this help.

    There will never be 2 rows return from the query and the good news is that the query is working fine for me.

    Thanks Again.
  • 11. Re: Need a help
    9876564 Newbie
    Currently Being Moderated
    Alberto Faenza wrote:
    Hi AbSheik,

    what about something like this:
    SELECT empno, dept
    , NVL(hiredate, (SELECT hiredate 
    FROM emp e2  
    WHERE e2.hiredate IS NOT NULL
    AND e1.empno = e2.empno
    AND e2.datefrom = (SELECT MIN(datefrom) 
    FROM emp e3
    WHERE e3.empno = e2.empno
    AND e3.hiredate IS NOT NULL
    )
    )
    ) AS hiredate  
    FROM emp e1
    WHERE TO_DATE('12-Sep-2002', 'DD-Mon-YYYY') BETWEEN datefrom AND dateto;
    {code}
    
    i.e.:
    
    {code:sql}
    with emp AS
    (
    SELECT 123 empno, NULL                                hiredate , 'A' dept, to_date('12/12/2001', 'DD/MM/YYYY') datefrom, to_date('12/12/2004', 'DD/MM/YYYY') dateto FROM DUAL UNION ALL
    SELECT 123 empno, NULL                                hiredate , 'B' dept, to_date('12/12/2004', 'DD/MM/YYYY') datefrom, to_date('12/12/2007', 'DD/MM/YYYY') dateto FROM DUAL UNION ALL
    SELECT 123 empno, to_date('12/12/2001', 'DD/MM/YYYY') hiredate , 'C' dept, to_date('12/12/2007', 'DD/MM/YYYY') datefrom, to_date('12/12/2008', 'DD/MM/YYYY') dateto FROM DUAL UNION ALL
    SELECT 123 empno, to_date('12/12/2002', 'DD/MM/YYYY') hiredate , 'D' dept, to_date('12/12/2008', 'DD/MM/YYYY') datefrom, to_date('12/12/2009', 'DD/MM/YYYY') dateto FROM DUAL
    )
    SELECT empno, dept
    , NVL(hiredate, (SELECT hiredate 
    FROM emp e2  
    WHERE e2.hiredate IS NOT NULL
    AND e1.empno = e2.empno
    AND e2.datefrom = (SELECT MIN(datefrom) 
    FROM emp e3
    WHERE e3.empno = e2.empno
    AND e3.hiredate IS NOT NULL
    )
    )
    ) AS hiredate  
    FROM emp e1
    WHERE TO_DATE('12-Sep-2002', 'DD-Mon-YYYY') BETWEEN datefrom AND dateto;
    
    EMPNO DEPT HIREDATE 
    ---------- ---- ---------
    123 A    12-DEC-01
    1 row selected.
    {code}
    
    Is it possible that you have 2 rows having same datefrom for the same empno? 
    In this case which one will you take?
    
    Or that you don't find any row with hiredate NOT NULL?
    
    Regards.
    Al
    
    Edited by: Alberto Faenza on Oct 11, 2012 3:17 PM
    Added some clarification
      database version : 11.2.0.2.0
    The result set is as per the requirement but as i have mentioned earlier that the table emp is having a huge amount of data , will it be good to hit this table thrice as we are hitting in the above query.
    i have written the following query
     
    WITH k AS 
    (SELECT * FROM (
    SELECT hire_date, 
    ROW_number() over (PARTITION BY emp_no ORDER BY date_from) ron
    FROM ( 
    SELECT emp_no,Dept_no,hire_date,date_from,date_to
    FROM emp
    WHERE HIRE_DATE IS NOT null))
     WHERE ron =1) 
    SELECT dept_no , nvl(abhi1.hire_date,k.hire_date)
    FROM emp,k
    WHERE ( '12-Sep-2002' 
    BETWEEN date_from AND date_to) OR k.hire_date IS NULL
    Not getting much information (m not that good in reading explain plan) from the explain plans for the above queries , so not sure which one will have the better performace.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points