10.9 Get a sequence of workdays
List a sequence of workdays between two dates.
List the name of each person on duty in each workday between 2020/04/27 and 2020/05/08. Below is part of the data in Duty table:
| ID | Date | Name |
|---|---|---|
| 1 | 2020/04/27 | Emily |
| 2 | 2020/04/28 | Emily |
| 3 | 2020/04/28 | Johnson |
| 4 | 2020/04/29 | Emily |
| 5 | 2020/04/30 | Johnson |
| … | … | … |
workdays(b,e,h) function gets a sequence of workdays between date b and date e inclusive. 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 | [2020/04/27,2020/05/08] |
| 2 | =workdays(A1(1),A1(2),[date(“2020/05/01”)]) |
| 3 | =T(“Duty.txt”) |
| 4 | =A3.align@a(A2, Date) |
| 5 | =A4.new(~.Date:Date, ~.(Name).concat@c():Names) |
A1 Define the start date and the end date.
A2 The workdays() function finds workdays within the date interval, excluding the May 1st holiday.
A3 Import Duty table.
A4 Perform alignment grouping on Duty table according to the sequence of workdays. Each workday will compare with all records.
A5 Create a new table sequence, where names of people on duty are concatenated with the comma.
Execution result:
| Date | Names |
|---|---|
| 2020/04/27 | Emily |
| 2020/04/28 | Emily,Johnson |
| 2020/04/29 | Emily |
| … | … |
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
