Create a table from another

[ecabanas]

Hi all,

I'm in a trouble. I have a table like this:

ItemId	From	To	Tarifa
10357856	01/02/2016	29/02/2016	0,88
10357856	01/03/2016	31/12/2016	0,52

I would like to create a table For every month an ID ItemId+Date like this:

Date	ID	Price
28/02/2016	10357856-42428	0,88
31/03/2016	10357856-42460	0,52
30/04/2016	10357856-42490	0,52
31/05/2016	10357856-42521	0,52
30/06/2016	10357856-42551	0,52
31/07/2016	10357856-42582	0,52
31/08/2016	10357856-42613	0,52
30/09/2016	10357856-42643	0,52

Many many thank's again

Eduard

[sunny_talwar · Accepted solution]

May be like this:

Table:

LOAD *,

  Date(Floor(MonthEnd(From, IterNo() - 1))) as Date,

  ItemId & '-' & Floor(MonthEnd(From, IterNo() - 1)) as ID

While Floor(MonthEnd(From, IterNo() - 1)) <= To;

LOAD * INLINE [

    ItemId, From, To, Tarifa

    10357856, 01/02/2016, 29/02/2016, "0,88"

    10357856, 01/03/2016, 31/12/2016, "0,52"

];

[Gysbert_Wassenaar]

NewTable:

LOAD

     Date(MonthStart(From,IterNo())-1) as Date,

     ItemId & '-' & num(MonthStart(From,IterNo())-1) as ID,

     Price

FROM

     ...source_table

WHILE

     MonthStart(From,IterNo())-1 < To

     ;

If From and To are strings instead of date then first use the date# function on them to turn the strings into dates:

date#(From, 'DD/MM/YYYY')

date#(To, 'DD/MM/YYYY')

[ecabanas]

Great Sunny as always!!!

Thank you