4 Replies Latest reply: Oct 15, 2012 1:48 AM by GauravBhide RSS

    Outer join

    muzaffar
      I have two table, employee (which is master table) & attendance table structure is given below).

      Create table employee( empid number, ename varchar2(50));

      create table attendance(empid number, punchdate date, timein date, timeout date);

      only present employees are recorded in attendance table date-wise, now i want to fetch the data of both present and absent employee date-wise, i can fetch data for a single date by this query.

      select e.empid,e.ename,a.punchdate,a.timein,a.timout
      from employee e = attendance a
      where e.empid (+) = a.empid;

      But i want to fetch the data of both absent and present employee date-wise in a range data is not fetched.

      select e.empid,e.ename,a.punchdate,a.timein,a.timout
      from employee e = attendance a
      where e.empid (+) = a.empid
      and a.punchdate between '01-oct-2012' and '12-oct-2012';


      is there anyway to do it? because i don't want to insert the absent employee data via any procedure as my manager is not allowing that.
        • 1. Re: Outer join
          rp0428
          >
          But i want to fetch the data of both absent and present employee date-wise in a range data is not fetched.
          >

          You can generate the dates you need using a query like this
          SELECT TO_DATE ('01-oct-2012', 'DD-MON-YYYY') + LEVEL - 1  myDatesFROM DUAL
          CONNECT BY LEVEL < 13
          
          MYDATE
          10/1/2012
          10/2/2012
          10/3/2012
          10/4/2012
          10/5/2012
          10/6/2012
          10/7/2012
          10/8/2012
          10/9/2012
          10/10/2012
          10/11/2012
          10/12/2012
          Then you use the above query as a table expression and join it to your employee table to generate a record for every employee for each of those dates.

          You can also use a method called 'Data Densification'. See chap 21 SQL for Analysis and Reporting in the Data Warehousing Guide.
          http://docs.oracle.com/cd/E14072_01/server.112/e10810/analysis.htm#i1014934

          You can use Partition Join Syntax and there is a sample showing how to do it.
          • 2. Re: Outer join
            HuaMin Chen
            Try
            select e.empid,e.ename,a.punchdate,a.timein,a.timout
            from employee e, attendance a
            where e.empid = a.empid(+);

            Edited by: HuaMin Chen on Oct 12, 2012 10:44 AM
            • 3. Re: Outer join
              rp0428
              Nonsense! That doesn't generate any records for missing dates.
              • 4. Re: Outer join
                GauravBhide
                Hi,

                Try this.
                with table_emp as
                (
                 select 1 as empid , 'A' as ename from dual
                 union all
                 select 2 as empid , 'B' as ename from dual
                 union all
                 select 3 as empid , 'C' as ename from dual
                 union all
                 select 4 as empid , 'D' as ename from dual
                 union all
                 select 5 as empid , 'E' as ename from dual
                ),
                table_attendence as
                (
                  select 1 as empid , to_date('01-oct-2012','dd-mon-yyyy') as punchdate , to_date('01-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('01-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all
                  select 2 as empid , to_date('01-oct-2012','dd-mon-yyyy') as punchdate , to_date('01-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('01-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all
                  select 3 as empid , to_date('01-oct-2012','dd-mon-yyyy') as punchdate , to_date('01-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('01-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all 
                  select 4 as empid , to_date('02-oct-2012','dd-mon-yyyy') as punchdate , to_date('02-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('02-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all
                  select 5 as empid , to_date('02-oct-2012','dd-mon-yyyy') as punchdate , to_date('02-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('02-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all
                  select 1 as empid , to_date('02-oct-2012','dd-mon-yyyy') as punchdate , to_date('02-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('02-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all
                  select 2 as empid , to_date('03-oct-2012','dd-mon-yyyy') as punchdate , to_date('03-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('03-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all  
                  select 3 as empid , to_date('03-oct-2012','dd-mon-yyyy') as punchdate , to_date('03-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('03-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all  
                  select 4 as empid , to_date('03-oct-2012','dd-mon-yyyy') as punchdate , to_date('03-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('03-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all  
                  select 5 as empid , to_date('04-oct-2012','dd-mon-yyyy') as punchdate , to_date('04-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('04-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all  
                  select 1 as empid , to_date('04-oct-2012','dd-mon-yyyy') as punchdate , to_date('04-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('04-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                  union all  
                  select 2 as empid , to_date('04-oct-2012','dd-mon-yyyy') as punchdate , to_date('04-oct-2012 10:00','dd-mon-yyyy hh:mi') as timein ,to_date('04-oct-2012 6:00','dd-mon-yyyy hh:mi') as timeout from dual
                ),
                table_all_dates as
                (
                select distinct empid ,ename, to_date('01-oct-2012','dd-mon-yyyy')+ level -1 as attendence_date from dual,table_emp connect by level <= to_date('04-oct-2012','dd-mon-yyyy') - to_date('01-oct-2012','dd-mon-yyyy') + 1
                )
                select b.attendence_date , b.empid , b.ename , a.empid  , case when a.empid is null then 'Absent' else 'Present' end attendence
                from table_attendence a right outer join table_all_dates b on a.empid = b.empid and a.punchdate = b.attendence_date order by 1,2;