Employees who have taken leave for more than two days three times consecutively a year
The following is the LeaveRecords table for employee leave records of a certain company in 2009. The start and end dates of each employee's leave are recorded:
The following table is a detailed information table of employees:
Please identify employees who have taken leave for more than 2 days for 3 consecutive times during this year.
Firstly, calculate the leave time for each record in the leave table, and then use cross row calculation to find the situation where the leave time for the three consecutive records is greater than 2 days.
| A | |
|---|---|
| 1 | =T("LeaveRecords.txt") |
| 2 | =T("Employees.txt").keys(ID) |
| 3 | =A1.group(Employee;~.(EndDate-StartDate):Length) |
| 4 | =A3.select(Length.pselect(~>2 && ~[-1]>2 && ~[-2]>2)>0) |
| 5 | =A4.(A2.find(Employee).Name) |
https://try.esproc.com/splx?3rY
A1 reads the leave record, A2 reads the employee information.
A3 groups the leave table by employee and generate a new table sequence. The number of days each employee takes leave is calculated and stored in the Length field as a sequence:
A4 uses select to filter out records in Length that have taken leave for more than 2 days for 3 consecutive times:
A5 searches for the corresponding names in the employee table based on the found employee IDs:
