Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good afternoon!
I have a problem and I do not know how to solve it.
I need to know how many days the product was left in stock each month.
I have the following table:
ID_PRODUCT | DATE_START | DATE_END | QT_DAYS |
1 | 01/03/2017 | 18/04/2017 | 48 |
I need to transform in the following table:
ID_PRODUCT | MONTH/YEAR | QT_DAYS |
1 | Mar/2017 | 30 |
1 | Apr/2017 | 18 |
How can I do this?
Thanks
Try like this
Table:
LOAD ID_PRODUCT,
MonthName(MonthStart(DATE_START, IterNo() -1)) as MonthYear,
RangeMin(DATE_END+1, Floor(MonthEnd(DATE_START, IterNo() -1))) - RangeMax(DATE_START, MonthStart(DATE_START, IterNo() -1)) as QT_DAYS
While ((Year(DATE_START)*12) + Month(DATE_START)) - 1 + IterNo() <= (Year(DATE_END)*12) + Month(DATE_END);
LOAD * INLINE [
ID_PRODUCT, DATE_START, DATE_END, QT_DAYS
1, 01/03/2017, 18/04/2017, 48
];
Try like this
Table:
LOAD ID_PRODUCT,
MonthName(MonthStart(DATE_START, IterNo() -1)) as MonthYear,
RangeMin(DATE_END+1, Floor(MonthEnd(DATE_START, IterNo() -1))) - RangeMax(DATE_START, MonthStart(DATE_START, IterNo() -1)) as QT_DAYS
While ((Year(DATE_START)*12) + Month(DATE_START)) - 1 + IterNo() <= (Year(DATE_END)*12) + Month(DATE_END);
LOAD * INLINE [
ID_PRODUCT, DATE_START, DATE_END, QT_DAYS
1, 01/03/2017, 18/04/2017, 48
];
Hi,
one solution could be also:
table1:
LOAD RangeMin(DATE_END,Floor(MonthEnd([MONTH/YEAR])))-RangeMax(DATE_START,[MONTH/YEAR])+1 as QT_DAYS, *;
LOAD MonthName(DATE_START,IterNo()-1) as [MONTH/YEAR], *
INLINE [
ID_PRODUCT, DATE_START, DATE_END
1, 01/03/2017, 18/04/2017
2, 10/03/2017, 28/05/2017
3, 20/04/2017, 30/06/2017
4, 30/05/2017, 31/08/2017
] While MonthName(DATE_START,IterNo()-1)<=DATE_END;
hope this helps
regards
Marco