11 Replies Latest reply: Oct 12, 2012 1:40 AM by 9876564 RSS

    Need a help

    9876564
      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
          Check for FIRST_VALUE analytic function
          • 2. Re: Need a help
            Frank Kulash
            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
              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
                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
                  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
                    Please read the FAQ

                    {thread:id=2174552}

                    {message:id=9360002}
                    {message:id=9360003}
                    • 7. Re: Need a help
                      AlbertoFaenza
                      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
                        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
                          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
                            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
                              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.