CODEWITHSUNDEEP
sqlgoogle-bigquery
Srinivas Ch
Srinivas Ch

Reputation: 31

How to generalize or parameterize the SQL query

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()
      )

Upvotes: 0

Views: 185

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery Standard SQL 

#standardSQL
SELECT emp_id, ename,
  EXTRACT(YEAR FROM IF(effective_end_date >= CURRENT_DATE, CURRENT_DATE, effective_start_date)) year_bucket
FROM `project.dataset.employee_det`
WHERE worker_status = 'Active' 
AND manager_name LIKE '%srinivas%'

You can test, play with above using dummy data as in example below 

#standardSQL
WITH `project.dataset.employee_det` AS (
  SELECT 1 emp_id, 'employee1' ename, DATE '2017-01-01' effective_start_date, DATE '2018-02-02' effective_end_date, 'Active' worker_status, 'srinivas' manager_name UNION ALL
  SELECT 2, 'employee2', '2018-01-02', '2019-01-15', 'Active', 'srinivas' UNION ALL
  SELECT 3, 'employee3', '2017-01-01', '2019-04-15', 'Active', 'srinivas' 
)
SELECT emp_id, ename,
  EXTRACT(YEAR FROM IF(effective_end_date >= CURRENT_DATE, CURRENT_DATE, effective_start_date)) year_bucket
FROM `project.dataset.employee_det`
WHERE worker_status = 'Active' 
AND manager_name LIKE '%srinivas%'

with result 

Row emp_id  ename       year_bucket  
1   1       employee1   2017     
2   2       employee2   2018     
3   3       employee3   2019

Update - excluding employees whose start and end YEAR is the same

You can just use one "generic" clause as below 

WHERE EXTRACT(YEAR FROM effective_start_date) != EXTRACT(YEAR FROM effective_end_date)

so, the whole query now will be as in below example 

#standardSQL
WITH `project.dataset.employee_det` AS (
  SELECT 1 emp_id, 'employee1' ename, DATE '2017-01-01' effective_start_date, DATE '2018-02-02' effective_end_date, 'Active' worker_status, 'srinivas' manager_name UNION ALL
  SELECT 2, 'employee2', '2018-01-02', '2019-01-15', 'Active', 'srinivas' UNION ALL
  SELECT 3, 'employee3', '2017-01-01', '2019-04-15', 'Active', 'srinivas' UNION ALL
  SELECT 4, 'employee4', '2017-01-01', '2017-04-15', 'Active', 'srinivas' 
)
SELECT emp_id, ename,
  EXTRACT(YEAR FROM IF(effective_end_date >= CURRENT_DATE, CURRENT_DATE, effective_start_date)) year_bucket
FROM `project.dataset.employee_det`
WHERE worker_status = 'Active' 
AND manager_name LIKE '%srinivas%'
AND EXTRACT(YEAR FROM effective_start_date) != EXTRACT(YEAR FROM effective_end_date)

with result 

Row emp_id  ename       year_bucket  
1   1       employee1   2017     
2   2       employee2   2018     
3   3       employee3   2019

as you can see - employee4 is not included in any bucket

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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

Upvotes: 2

Related Questions