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

Question

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

question from:https://stackoverflow.com/questions/65896914/how-can-we-find-the-employees-has-ever-taken-a-fiscal-year-long-vacation-or-just

Answer 1

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