How can we find the employees has ever taken a fiscal-year long vacation or just more?

user_11467764 0
Viewed 67

How can we find the employees has ever taken a fiscal year long vacation with one query? This particular company consider its fiscal year duration roughly consists of the fall quarter of current year , the winter quarter and the spring quarter of the incoming/ (its) next year.

let's say we have a table contains all employees as eid(string).

Then a table a schedule table (instead of vacation table) as eid (string), departid (string), quarter (string), year (integer). We are given a schedule table to find out all the employees take fiscal-year long vacation or just more .

employees
+-----+
| id  | 
+-----+ 
| e01 | 
+-----+ 
| e02 |  
+-----+ 
| e03 | 
+-----+ 
| e04 | 
+-----+ 


schedule
+----+-------------+-------------+-------------+
| eid| departid    | quarter     |   year      |
+----+-------------+-------------+-------------+
| e01| marketing01 | Winter      |   2013      |
+----+-------------+-------------+-------------+
| e01| marketing01 | Fall        |   2013      |
+----+-------------+-------------+-------------+
| e01| marketing01 | Fall        |   2013      |
+----+-------------+-------------+-------------+
| e01| marketing01 | Fall        |   2015      |
+----+-------------+-------------+-------------+
| e01| marketing01 | Fall        |   2015      |
+----+-------------+-------------+-------------+
| e01| marketing01 | Fall        |   2015      |
+----+-------------+-------------+-------------+
| e01| marketing01 | Fall        |   2015      |
+----+-------------+-------------+-------------+
| e02| theboard00  | Spring      |   2017      |
+----+-------------+-------------+-------------+
| e02| theboard00  | Winter      |   2019      |
+----+-------------+-------------+-------------+
| e02| theboard00  | Winter      |   2020      |
+----+-------------+-------------+-------------+
| e02| enginering01| Winter      |   2020      |
+----+-------------+-------------+-------------+
| e03| marketing01 | Spring      |   2018      |
+----+-------------+-------------+-------------+
| e03| marketing01 | Spring      |   2020      |
+----+-------------+-------------+-------------+
| e03| marketing01 | Spring      |   2020      |
+----+-------------+-------------+-------------+
| e04| marketing01 | Spring      |   2019      |
+----+-------------+-------------+-------------+
| e04| marketing01 | Spring      |   2020      |
+----+-------------+-------------+-------------+
| e04| marketing01 | Spring      |   2020      |
+----+-------------+-------------+-------------+
| e04| marketing01 | Spring      |   2018      |
+----+-------------+-------------+-------------+
| e05| marketing01 | Spring      |   2020      |
+----+-------------+-------------+-------------+
| e05| marketing01 | Spring      |   2001      |
+----+-------------+-------------+-------------+
| e06| marketing01 | Fall        |   2016      |
+----+-------------+-------------+-------------+
| e06| marketing01 | Fall        |   2018      |
+----+-------------+-------------+-------------+
| e07| theboard00  | Spring      |   2016      |
+----+-------------+-------------+-------------+
| e07| theboard00  | Fall        |   2017      |
+----+-------------+-------------+-------------+

The query should return e02, e03, e05, e06, e07, since

e02 work Spring 2017, Winter 2019 (more than year-long vacation)
e03 work Winter 2018, Fall   2020 (more than year-long vacation)
e05 work Fall   2001, Fall   2020 (way more than year-long vacation)
e06 work Fall   2016, Fall   2018 (way more than year-long vacation)
e07 work Spring 2016, Fall   2017 (year-long vacation)

What I have so far, not much, but blocked by one thing:

Select eid
From (bad employees)
 
bad employees=
Select * 
From vacation, employees
where  
group by eid, year, quarter

I get stuck coming a validation for fiscal-year long vacation. Any pointer is appreciated

sqlite nested
2 Answers

I think this would help.

with quarter_to_num as (select '01-01' start_date, 'Winter' q_name
                        union all
                        select '04-01' start_date, 'Spring' q_name
                        union all
                        select '07-01' start_date, 'Summer' q_name
                        union all
                        select '10-01' start_date, 'Fall' q_name)

select eid
  from (select t.eid, 
               date(t.year || '-' || qtn.start_date) start_date,
               -3*row_number() over(partition by t.eid order by t.eid, date(t.year || '-' || qtn.start_date)) rn
          from vacation t
          join quarter_to_num qtn on t.quarter = qtn.q_name
         order by 1, 3)
 group by eid, date(start_date, rn || ' months')
having count(1) >= 3;

The query might seem a bit tricky, so I'll explain it in details.

First of all I need to convert dates to somewhat comparable. Since it's not impossible for db to say whether Spring-2019 is greater that Summer-2020 that has to be "casted" to dates/integers. This is what cte (the "with" clause) with dummy dates is here for.

Now, having the dates instead of strings (start_date column) we are able to compare things in a proper manner. In order to do that I've numbered all the lined having same eid ordered by start_date. Thus the intermediate result for eid was like eid start_date row_number e01 2010-09-01 1 e01 2011-01-01 2 e01 2011-04-01 3 e02 2008-09-01 1

This is what the whole subquery is here for.

So now only thing left to do is to make "start dates" equal to each other for grouping. For that purpose from each date I substract (3 months * corresponding row_number). The result will be like below.

eid dummy_date e01 2010-06-01 e01 2010-06-01 e01 2010-06-01 e02 2008-06-01

Now when "dummy_date" col is filled with equal values it is easy to find an employee that took vacation in all 3 sequential periods as mentioned in your example. That what has been done in "having" clause of the main query.

In fact, the sub-query is not needed here. I left it just to achieve better readability.

user_60336010

You can do it with a self join, aggregation and a condition in the HAVING clause:

SELECT v1.eid
FROM vacation v1 INNER JOIN vacation v2
ON v1.year = v2.year - 1
WHERE v1.quarter = 'Fall' AND v1.quarter <> v2.quarter
GROUP BY v1.eid
HAVING COUNT(*) = 2

And if you want the details of the employees:

SELECT * FROM employees
WHERE id IN (
  SELECT v1.eid
  FROM vacation v1 INNER JOIN vacation v2
  ON v1.year = v2.year - 1
  WHERE v1.quarter = 'Fall' AND v1.quarter <> v2.quarter
  GROUP BY v1.eid
  HAVING COUNT(*) = 2
)

See the demo.
Results:

id
e01
user_104988280
Related