yandex
loader

please wait

  • Shreya Bansal Mar-08-2019 12:48:35 PM ( 2 months 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()
          )
  • Lucky Negi Mar-08-2019 12:51:28 PM ( 2 months ago )

    You seem to want the start year for employees who have ended and the current year for active employees. So:

    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%';

Please login

Similar Discussion

Recommended For You