Convert monthly data by location and year to daily

[regowins]

Hi,

I have a budget table by month by location going back 3 years...( below & attached sample data) .

DPT_ID	MONTH_DATE	LOCATION	BUDGETED	DATE_YEAR	DATE_MONTH
1	1/1/2012	21	555	2012	1
2	2/1/2012	21	856	2012	2
3	3/1/2012	21	678	2012	3
4	4/1/2012	21	425	2012	4
5	5/1/2012	21	786	2012	5
6	6/1/2012	21	786	2012	6
7	7/1/2012	21	422	2012	7
8	8/1/2012	21	786	2012	8
9	9/1/2012	21	435	2012	9
10	10/1/2012	21	453	2012	10
11	11/1/2012	21	963	2012	11
12	12/1/2012	21	424	2012	12

I need to recreate the exact same table  as above  but convert the monthly budget to daily for each month by location. so for example, I would take Jan 555 / 31 = 17.90 and then generate 31 dates with 17.9 for daily budget ....Feb 856/29 = 29.5 for 29 days... till the end of the year.   Then loop for the next location / Year and monthly budget to convert to daily.

Any idea how I would go about doing this in the script?

Thanks for the help!

[jagan · Accepted solution]

Hi,

Try this

Data:

LOAD

  DPT_ID,

     MONTH_DATE,

     LOCATION,

     BUDGETED,     

     DATE_YEAR,

     DATE_MONTH,

  BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,

  Date(MONTH_DATE + IterNo() - 1) AS DATE

WHILE Num(MonthStart + IterNo() - 1) <= MonthEnd;

LOAD DPT_ID,

     Date(MONTH_DATE) AS MONTH_DATE,

     LOCATION,

     BUDGETED,     

     DATE_YEAR,

     DATE_MONTH,

     Num(MonthEnd(Date(MONTH_DATE))) AS MonthEnd,

     Num(MonthStart(Date(MONTH_DATE))) AS MonthStart    

FROM

(ooxml, embedded labels, table is [ Sample]);

[regowins]

Your solution was perfect!  Thank You!!!!

[tresesco]

Nice solution Jagan!

however, a small but significant bug is there in this solution.

BUDGETED/ Num(MonthEnd - MonthStart + 1) AS Budget_Daily,

MonthEnd would have last millisecond of the last date of the month which would result a difference (MonthEnd-MonthStart) by extra 1 (0.99..). That is for ex. - the January value will be divided by 32 (31.99...) instead of 31. You can handle this by two alternate ways:

BUDGETED/ (Floor(MonthEnd) - MonthStart + 1) AS Budget_Daily

Or,

BUDGETED/ Day(MonthEnd) AS Budget_Daily

[regowins]

Thanks for the catch Tresesco!