Autogenerate rows from start date to end date

[emmanueld]

Hello community,

I'm wondering if there is an existing specific function to do what I want to do:

I have in my database x rows of data with a start date and an end date.

I want as output to have 1 row per month between the start and the end date, and that for each row of input.

Ex : if row 1 has : start date = 2016/01/12 and end date = 2016/03/21, then I want to have one row for january, one row for february, one row for march. And this functionning for each input row.

All I can think of is using FOR...NEXT or FOR EACH, but this seems to take too long.

Thanks

I will explain the context in case there is some other way of doing it

[emmanueld]

Context:

each row of data as input is a study, with a start date and an end date.

The indicator I need is the number of studies running, during each month, or each year.

So a study starting on 2016/01/12 and ending on 2016/03/21 would count :

- for 1 on dimension Month = january

- for 1 on dimension Month = february

- for 1 on dimension Month = march

- for 3/12 on dimension Year = 2016

Therefore I was planning to duplicate the rows to have them at month level, and to just count the rows to calculate the indicator (and divide by 12 if at Year level)

[#3]

try with "Crosstable"

[emmanueld]

Thank you  Khawar. I didn't know that one and it is very interesting. However I don't see how I could make use of it in my case, because I don't have a crosstable, and I cannot turn my data into a crosstable.

Indeed I can't predefine the number of month between the start date and the end date, so I wouldn't know how many columns of months I would need in my crosstable.

I'm looking at intervalmatch though, it looks like something I could use!

[MarcoWedel]

Hi,

one solution to just duplicate the source rows with different months could be:

LOAD RecNo() as ID, *,
    MonthName(StartDate,IterNo()-1) as Month
INLINE [
    StartDate, EndDate
    2016/01/12, 2016/03/21
    2016/02/10, 2016/05/26
    2016/03/01, 2016/04/05
    2016/01/12, 2016/03/21
]
While MonthName(StartDate,IterNo()-1)<=EndDate;

But your description sounds like linking your table with a master table using the intervalmatch prefix is the better idea.

hope this helps

regards

Marco

[emmanueld]

Thank you very much Marco, I will look into that, it looks great!