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)|
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|
(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!