You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Span of employees under a manager for particular time period

3 replies [Last post]
Guest's picture
Guest

I need to select all the employees under a manager in a particular span of time period. Say I need to select full span of employees who were under "12345" between 01/JAN/2009 to 07/MAR/2009. Employees who were under "12345" only till 05/JAN/2009 and got shifted to another department should also get listed in the output and same case for terminated employee. I am using the below SQL, but its not fetching correct employee. Pls let me know how to achive this.

Select a.emplid, a.supervisor_id from PS_job a
START WITH a.supervisor_id = '12345'
CONNECT BY PRIOR a.emplid = a.supervisor_id
and a.empl_status in ('A','L','P','S')
and a.effdt = ( (Select Max(Effdt) from
ps_job where emplid = a.emplid
and empl_rcd = a.empl_rcd and effdt <= 07/MAR/2009)

or a.effdt = (Select Max(Effdt) from ps_job
where emplid = a.emplid and empl_rcd = a.empl_rcd
and (effdt >= 01/JAN/2009 and effdt <= 07/MAR/2009)
and a.empl_status in ('A','L','P','S')))

Lepa's picture
User offline. Last seen 2 years 20 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Span of employees under a manager for particular time period

Try the below for your dates:

to_date('07/12/2009', 'dd/mm/yyyy')

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Guest's picture
Guest (not verified)
Re: Span of employees under a manager for particular time period

There is no problem with the date format, its issue with the logic, bcoz its not fetching the correct employees

Guest's picture
Guest (not verified)
Re: Span of employees under a manager for particular time period

Pls let me know how to give effective date and other conditions, while using "connect by prior" in a sql.