Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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"
];
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"
];
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')
Great Sunny as always!!!
Thank you