How to generalize or parameterize the SQL query

manpreet Best Answer 2 years ago

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 
from employee_det 
where worker_status = 'Active' 
  and manager_name like '%srinivas%'
  and (
        ( date(effective_start_date) <= '2017-12-31' 
         and date(effective_end_date)>='2017-12-31' ) 
        or 
        ( date(effective_start_date) <= '2018-12-31' 
         and date(effective_end_date)>='2018-12-31' ) 
        or
        ( date(effective_start_date) <= current_date() 
         and date(effective_end_date)>=current_date()
      )

manpreet 2 years ago

select emp_id, ename,
       (case when effective_end_date > current_date
             then year(current_date)
             else year(effective_start_date)
        end) as year_bucket
from employee_det
where worker_status = 'Active' and
      manager_name like '%srinivas%';