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

    Unanswered: Query needed urgently :- fetching latest status of Employees

    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
    that is some odd logic. there are always many ways to get what you want with SQL

    select employee, max(case when enddate is null then 'inactive' else 'active' end)

    You can use a subselect to get the max,
    ....
    Dave

Posting Permissions

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