10.7 Get the date N months before
Find the date N months before.
Query the total sale amount of three months before 2014/05/21. Below is part of the sale data:
| ORDERID | CUSTOMERID | EMPLOYEEID | ORDERDATE | AMOUNT |
|---|---|---|---|---|
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10401 | HANAR | 1 | 2014/01/01 | 3868.6 |
| 10402 | ERNSH | 8 | 2014/01/02 | 2713.5 |
| 10403 | ERNSH | 4 | 2014/01/03 | 1005.9 |
| 10404 | MAGAA | 2 | 2014/01/03 | 1675.0 |
| … | … | … | … | … |
SPL supplies elapse(dateExp, n) function to get the date a certain time period before/after. The function gets date before n days/months/years before when n is negative. It uses @m to get a date n months before or after a date specified.
SPL script:
| A | |
|---|---|
| 1 | =connect(“db”) |
| 2 | =A1.query@x(“select * from Sales”) |
| 3 | =date(“2014/05/21”) |
| 4 | =A2.select(OrderDate>=elapse@m(A3,-3) && OrderDate<A3) |
| 5 | =A4.sum(Amount) |
A1 Connect to data source.
A2 Import Sales table.
A3 Define a date.
A4 Use elapse() function to get a date 3 months before A3’s date, and select records within the target 3 months.
A5 Calculate total sale amount in the 3 months.
Execution result:
| Value |
|---|
| 154074.49 |
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
