10.8 Get the date after n workdays
Find the date after N workdays.
For each employee, find the number of cases when customer complaints are not solved within 10 workdays in the year 2014. Below is part of the source data:
| ID | CustomerID | EmployeeId | QuestionDate | SolveDate |
|---|---|---|---|---|
| 1 | OLDWO | 2 | 2014/01/01 | 2014/01/09 |
| 2 | WELLI | 7 | 2014/01/01 | 2014/01/07 |
| 3 | LAUGB | 2 | 2014/01/01 | 2014/01/07 |
| 4 | LINOD | 8 | 2014/01/02 | 2014/01/08 |
| 5 | REGGC | 5 | 2014/01/02 | 2014/01/12 |
| … | … | … | … | … |
workday(t,k,h) function finds the date k workdays before or after date t. Parameter h is a sequence of (non)holidays, whose members are either weekends or holidays. If they are weekends, treat them as shifted workdays.
SPL script:
| A | |
|---|---|
| 1 | =T(“AfterSale.csv”;“,”) |
| 2 | [2014/01/01,2014/01/26,2014/01/31,2014/02/03,2014/02/04,2014/02/05,2014/02/06,2014/02/08,2014/04/07,2014/05/01,2014/05/02,2014/05/04,2014/06/02,2014/09/08,2014/09/28,2014/10/01,2014/10/02,2014/10/03,2014/10/06,2014/10/07,2014/10/11] |
| 3 | =A1.select(year(QuestionDate)==2014 && workday(QuestionDate, 10, A2) < SolveDate) |
| 4 | =A3.groups(EmployeeId; count(~):Count) |
A1 Import the AfterSale table.
A2 Define a sequence of holidays in the year 2014.
A3 Use workday() function to get the date after 10 workdays, excluding the holidays.
A4 Group records by employee and count the number of eligible cases.
Execution result:
| EmployeeID | Count |
|---|---|
| 1 | 2 |
| 2 | 1 |
| 3 | 2 |
| … | … |
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
