This discussion is archived
4 Replies Latest reply: Oct 14, 2012 11:48 PM by GauravBhide RSS

Outer join

muzaffar Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Nonsense! That doesn't generate any records for missing dates.
  • 4. Re: Outer join
    GauravBhide Newbie
    Currently Being Moderated
    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;

Legend

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