I would like to know the total number of vacation days for each employee in chronological order and the year in which the total number of vacation days exceeds 10.
It would be very helpful if you could give me some tips on how to think.
Data Affected
| Year | Employee ID | Days Off
+------+--------+-----------+
| 2014 | 00001 | 5
| 2015 | 00001 | 3
| 2016 | 00001 | 7
| 2017 | 00001 | 4
| 2014 | 00002 | 4
| 2017 | 00002 | 6
| 2019 | 00002 | 10
Expect Results
Employee ID | Year
-------+--------
00001 | 2016
00002 | 2017
I wrote it in the standard SQL range.
I have listed the total number of vacation days for an employee until a certain year, narrowed it down to more than 10 days, and wrote down the minimum number for the year.
CREATE TABLE IF NOT EXISTS PaidLeaveDays(
yeras int not null
, employee_id int not null
, pay_leave_days int not null
);
SELECT
MIN(pld10.years) AS years
, pld10.employee_id
from
(
SELECT
pld2.years
, pld2.employee_id
, sum(pld1.paid_leave_days)ASpaid_leave_days_total
from
PaidLeaveDays AS pld1
INNER JOIN PaidLeaveDays pld2
ON pld1.employee_id = pld2.employee_id
AND pld1.years<=pld2.years
GROUP BY
pld2.years
, pld2.employee_id
HAVING
pay_leave_days_total>=10
AS pld10
GROUP BY
pld10.employee_id;
years employee_id
2016 1
2017 2
© 2024 OneMinuteCode. All rights reserved.