Employees who have accumulated more than 5 years of work experience in New York
Here is the employee information table for a certain company called Employees:
Due to business reasons, the employees of this company have frequent regional transfers. To record the transfer history, they used the following table EmpTransfer, which records every job transfer of employees since joining the company (employees who have already resigned are not included in this table):
The date and target location of each transfer are recorded in it. Now please identify employees who have accumulated more than 5 years of work experience in New York.
The key to this problem is to transform the structure of the table recorded by transfer events to a table recorded by work time periods, so that the work time of each stage can be easily calculated and filtered.
| A | |
|---|---|
| 1 | =T("Employees.txt").keys(ID) |
| 2 | =T("EmpTransfer.txt") |
| 3 | >A2.switch(Employee,A1) |
| 4 | =A2.group(Employee;~.sum(if(Area=="New York", ifn(Date[1],now())-Date)): AccWorkDays_NYC) |
| 5 | =A4.select(AccWorkDays_NYC>=5*365) .new(Employee.Name:Employee, AccWorkDays_NYC\365:AccWorkYears_NYC) |
https://try.esproc.com/splx?2MV
A1 reads employee information, A2 reads employee transfer records.
A3 uses the switch function to replace all employee fields in the transfer table with the corresponding records in the employee table. This kind of processing in SPL often brings us great convenience in solving problems.
A4 groups employee transfer records by employee and calculates the total number of days the employee worked in New York, AccWorkDays_NYC, in a loop within each group. To do this, sum up all of the employee's transfer records and accumulate their work time transferred to New York. The start time of each transfer is the corresponding Date, and the end time is the Date of the next transfer record. If there is no next record, use now to obtain the current time as the end time.
The result of A4 is as follows:
A5 selects employees who have worked in AccWorkDays_NYC for over 5 years and compiles a result table. Retrieve the employee's name from the Employee field and calculate their total years of service in New York:
