Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: Selecting ids with null values.

    Hi,

    I have a table Employee with 3 columns.

    emp_Id | begindate | Enddate
    001 2013-01-01 2013-12-31
    001 2014-01-01 2014-12-31
    001 2015-01-01 null
    002 2013-01-01 2013-12-31
    002 2014-01-01 2014-12-31
    002 2015-01-01 null
    003 2013-01-01 2013-12-31
    003 2014-01-01 2014-12-31


    enddate gets updated after the end of every year. Employees are active when the enddate is not null against their ods. i.e. employee 001 and 002.

    When the enddate is null, the employees do not exist in the company anymore. Inactive. i.e. 003.

    I would like to fetch employeeId and their latest status.

    emp_id | status

    001 active
    002 active
    003 inactive

    Thank you in advance

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    you asked same question in other forum
    Dave

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    SQL> with test as
      2    (select 1 empid, date '2013-01-01' begindate, date '2013-12-31' enddate from dual union
      3     select 1, date '2014-01-01', date '2014-12-31' from dual union
      4     select 1, date '2015-01-01', null              from dual union
      5     select 3, date '2013-01-01', date '2013-12-31' from dual union
      6     select 3, date '2014-01-01', date '2014-12-31' from dual
      7    )
      8  select t.empid,
      9    case when t.enddate is null then 'active'
     10         else 'inactive'
     11    end status
     12  from test t
     13  where t.begindate = (select max(t1.begindate)
     14                       from test t1
     15                       where t1.empid = t.empid
     16                      )
     17  order by empid;
    
         EMPID STATUS
    ---------- --------
             1 active
             3 inactive
    
    SQL>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •