Number of days per month

[talita_verdes]

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

[sunny_talwar · Accepted solution]

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

];

[MarcoWedel]

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