Counting Employees by Month
【Question】
I have a table
PEOPLE, DATE, DELETED
Amanda,2015-03-01,Null
Ray,2015-03-01,Null
Moe,2015-04-01,Null
Yan,2015-05-01,Null
Bee,2015-05-05,2015-06-12
now I need to group it and sum it with months like this:
March:2 people
April:3
May:5
June:5
July:4
so new people should not be counted in previous month but they should be in next months for my range (January - June). And if man is DELETED, he should be counted together with another people last time in month when he has been deleted.
How to write query for this?
【Answer】
A JOIN subquery is needed in SQL to supply the missing months. It’s complicated. But it’s convenient to do it in SPL:
| A |
|
| 1 |
=connect(“demo”) |
| 2 |
=A1.query(“SELECT * FROM tb1”) |
| 3 |
=to(3,7).new(~:month,A2.count(month(DATE)<=month)-A2.count(DELETED && month(DELETED)<month):count) |
Result of executing the SPL script:
A1: Connect to the database;
A2: Retrieve the data;
A3: Count the employees in each month from March to July.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
