Hi,
I have a (probably) rather simple problem that I cannot figure out.
I have a data set containing the fields “InstallationNumberID”, “StartDate (YYYY.MM.DD)” and “EndDate (YYYY.MM.DD)”.
| InstallationNumberID | StartDate (YYYY.MM.DD) | EndDate (YYYY.MM.DD) |
|---|
| 1000 | 2017.06.01 | 2017.12.31 |
| 1001 | 2017.07.01 | 2017.11.30 |
| 1002 | 2017.06.01 | 2017.10.31 |
| 1003 | 2017.11.01 | 2017.11.30 |
| 1004 | 2017.10.01 | 2017.11.30 |
| 1005 | 2017.08.01 | 2017.09.30 |
| | |
What I need to do is to aggregate and calculate the number of active customers for each month. Something like this:
| Month | Count InstallationNumberID in delivery | Active InstallationNumberID |
|---|
Jun 2017 | 2 | 1000,1002 |
| Jul 2017 | 3 | 1000,1001,1002 |
| Aug 2017 | 4 | 1000,1001,1002,1005 |
| Sep 2017 | 4 | 1000,1001,1002,1005 |
| Oct 2017 | 4 | 1000,1001,1002,1004 |
| Nov 2017 | 4 | 1000,1001,1003,1004 |
| Dec 2017 | 1 | 1000 |
| Jan 2018 | 0 | |
(the column to the right is not necessary to display, it just shows what I imagine the logic would be like)
I have searched but haven’t found. I guess I need to aggregate and use in between for each row. And maybe create/connect to a third, more neutral calender field. Simply put another version of “count active customers”. But how do I get there?
The example data is quite simple. In reality I have several million rows with InstallationNumberID, so the solution must be efficient also for a larger data set.
Any help would be appreciated!
br,
J.
