Raqsoft 28 No.
17 View •
Counting Employees by Month
I have a table
PEOPLE, DATE, DELETED
now I need to group it and sum it with months like this:
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?
A JOIN subquery is needed in SQL to supply the missing months. It’s complicated. But it’s convenient to do it in SPL:
=A1.query(“SELECT * FROM tb1”)
=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.