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:

undefined

A1: Connect to the database;

A2: Retrieve the data;

A3: Count the employees in each month from March to July.