count per month between dates

[#1]

I have contracts with a startDate and endDate.

Id  |  startDate    | endDate

1   | 01.01.2011  | 31.12.2011

2   | 01.05.2011  | 31.12.2011

3   | 01.01.2011  | 31.12.2012

4   | 01.05.2011  | 31.12.2012

Now I need to count the active contracts per month.

Result Diagram:

01/2011 : 2

02/2011 : 2

03/2011 : 2

04/2011 : 2

05/2011 : 4

06/2011 : 5

…

How can I achieve that ?

Many thanks,

Marcel

[swuehl]

Are your startDate always at month's start and endDate always on month's end?

You could create a Table with Month start dates, then use an INTERVALMATCH to link your Month date table to the ID periods. Please check INTERVALMATCH LOAD prefix in the Help file.

Regards,

Stefan

[#3]

sadly the start and end dates can be any date.

[swuehl]

So does a contract count to July if the contract ends somewhere in the middle? Are there any additional rules?

My suggested approach should work if you are saying 'Nr of contracts at a certain date', then create the table of e.g. Month start dates.

[#5]

swuehl wrote:

So does a contract count to July if the contract ends somewhere in the middle? Are there any additional rules?

My suggested approach should work if you are saying 'Nr of contracts at a certain date', then create the table of e.g. Month start dates.

yes, a contract counts to July if it ends somewhere in the middle.

I did look at the INTERVALMATCH and it looks promising. But I actually have 2 date ranges (start/end of contract and start/end of month) and the INTERVALMATCH only checks if a single value is within a range.

Or maybe I don't get it... 😞

[swuehl · Accepted solution]

I still believe an Intervalmatch should work, but maybe there is an easier solution for you.

Try something like

TEST:

LOAD *,

addmonths(monthstart(startDate),iterno()-1) as ContractMonth

while monthstart(endDate) >= addmonths(monthstart(startDate),iterno()-1);

LOAD * INLINE [

Id  ,  startDate    , endDate

1   , 01.01.2011  , 31.12.2011

2   , 01.05.2011  , 31.12.2011

3   , 01.01.2011  , 31.12.2012

4   , 01.05.2011  , 31.12.2012

5   , 20.02.2011 , 1.12.2011

];

And then create a table with ContractMonth as dimension (or =MonthName(ContractMonth), if you like), and then use

=count(Id)

as expression.

[#7]

cool! it works! It generates a lot of data, but it works.

many thanks!