I have a problem with my employee_det table, where I am categorizing year wise active employee status. for example1: an employee joined in 01-01-2017 and released from company in 02-02-2018 then he/she fall under 2017 bucket.
example2: If an employee joined in 01-02-2018 and released in 01-15-2019 then he will be under 2018 bucket.
if an employee joined in 01-01-2017 and he is still continuing in company then he must fall under 2019.
I have written the following query and which is giving me accurate results, but next year I need to add one more entry in WHERE condition, instead of that is there is any generalized way to solve this.
select emp_id, ename, year(effective_start_date) as year_bucket
where worker_status = 'Active'
and manager_name like '%srinivas%'
( date(effective_start_date) <= '2017-12-31'
and date(effective_end_date)>='2017-12-31' )
( date(effective_start_date) <= '2018-12-31'
and date(effective_end_date)>='2018-12-31' )
( date(effective_start_date) <= current_date()