10.10 Get a sequence of dates
List a sequence of dates between two dates.
Count the days in multiple time intervals, excluding the overlapping periods. Below is part of the source data:
| ID | Start | End |
|---|---|---|
| 1 | 2012/07/04 | 2012/07/16 |
| 2 | 2012/07/06 | 2012/07/10 |
| 3 | 2012/07/19 | 2012/07/24 |
| 4 | 2012/07/22 | 2012/07/25 |
| 5 | 2012/07/30 | 2012/08/02 |
| … | … | … |
The periods(s,e,i) function returns a sequence of times with i interval between within a period starting from s to e inclusive. The default unit is day and default value of i is 1.
SPL script:
| A | |
|---|---|
| 1 | =T(“periods.txt”) |
| 2 | =A1.(periods(Start,End)) |
| 3 | =A2.union() |
| 4 | =A3.len() |
A1 Import periods table from the source file.
A2 Get dates in each period in loop.
A3 Union all dates.
A4 Count the dates.
Execution result:
A3:
| Members |
|---|
| 2017/07/06 |
| 2017/07/07 |
| 2017/07/08 |
| 2017/07/09 |
| 2017/07/10 |
| …… |
A4:
| Value |
|---|
| 52 |
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
