Autogenerate dates for each table row

[benjamins]

I have a table A with an Id and two date columns which describe each element's availabililty (begin and end):

Example:

Id	Begin	End
1	2016-01-01	2016-01-09
2	2016-01-01	2016-01-15
3	2016-01-11	2016-01-17

So the first element would be available from 1st of January to 9th of January and so on.

I'd like to create a table B from this information which contains all dates on which each element would be available.

So this table should have two columns "Id" and "ElementDateId" and contain 31 records total (9 for element 1, 15 for 2 and 7 for 3).

I'd have suspected something like the following (please also see the attached sample), however this doesn't seem to work out:

QV_A:

LOAD

ID

, date(Begin) as BeginDate

, date(End) as EndDate

Resident QV_TEMP_A;

Drop Table QV_TEMP_A;

QV_GENERATE_A:

LOAD ID,

     floor(EndDate) - floor(BeginDate) + 1 as num_days,

     floor(BeginDate) as floor_begin

Resident QV_A;

For vValueNo = 1 to num_days

     LOAD

  floor_begin + $(vValueNo) -1 as ElementDateId

AUTOGENERATE num_days;

next vValueNo;

Any ideas?

Thanks a lot

Benjamin

[petter]

did you look at this? How to generate missing dates

[petter · Accepted solution]

One of the quickest ways for your particular data set would be:

LOAD
  Id,
  Date(Begin + IterNo() - 1) AS Date
INLINE [
Id Begin End
1 2016-01-01 2016-01-09
2 2016-01-01 2016-01-15
3 2016-01-11 2016-01-17
] ( delimiter is \t)
WHILE
  IterNo() <= Num(End) - Num(Begin) +1;

If read from an external data source it would be:

LOAD
  Id,
  Date(Begin + IterNo() - 1) AS Date
WHILE
  IterNo() <= Num(End) - Num(Begin) +1;
SQL
     SELECT Id, Begin, End FROM myTable;

[benjamins]

If I understand that one correctly at first glance then it is about generating those dates once globally. I need to do this per table row which doesn't work out for me using a cursor.

[benjamins]

Perfect!

Works like a charm.

Thanks!