CODEWITHSUNDEEP
sqloracle-database
CoolArchTek
CoolArchTek

Reputation: 3829

Get employees that were active during specific date range

How do I get list of active employees who were active in a specific date range (1-JAN-2014 TO 31-MAR-2014)

My table is like Table: employeeheader

emphistory

If enddate is null that means employee is still active.

Upvotes: 0

Views: 1303

Answers (3)

schlonzo
schlonzo

Reputation: 1406

SELECT * FROM employeeheader
JOIN emphistory ON employeeheader.empid = emphistory.empid
WHERE begindate <= <range_start> AND (enddate is null OR enddate >= <range_end>)

This gives all the employees which worked completely during a specific range.

Upvotes: 1

Kiran Hegde
Kiran Hegde

Reputation: 3681

This query can help

SELECT * FROM employeeheader
WHERE empid IN
(SELECT empid FROM emphistory
 WHERE (enddate IS NULL OR enddate>= EndDateParameter) 
        AND begindate<=BeginDateParameter)

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

Something like this might work - I didn't actually test it.

where begindate < the day after your end date
and nvl(enddate, sysdate) >= your start date

Since oracle dates include the time of day, you want to account for it, just in case.

Upvotes: 0

Related Questions