MySQL user year report based on join date and left date-CodePudding

I have the MYSQL employees table like this:

ID	name	joining_date	date_left
1	ABC	2018-01-01	2019-04-30
2	DEF	2019-01-01	2020-12-31
3	GHI	2019-01-01	null
4	JKL	2021-11-30	2022-04-02

And I need output in below mentioned case in one single query.

1). If I want the report of year 2018 (Here, year 2018 means start date 2018-01-01 and end date 2018-12-31) In this case, only row with id 1 should be return

2). If I want the report of year 2019 In this case, row with id 1, 2 and 3 should be return

3). If I want the report of year 2020 In this case, row with id 2 and 3 should be return because for #3 date_left is null that means employee is not left

4). If I want the report of year 2021 In this case, row with id 3 and 4 should return because date_left is null for #3.

5). If I want the report of year 2022 In this case, row with id 3 and 4 should return because date_left is null for #3.

Can anyone please help in this MySQL query?

CodePudding user response：

You may treat this as the overlapping range problem, between joining and leaving date range, and a range covering an entire calendar year. For 2018, we can try:

SELECT *
FROM employees
WHERE ('2018-01-01' <= date_left OR date_left IS NULL) AND
      '2018-12-31' >= joining_date;

See the demo here for the 2021 calendar year.