Split records - Interval Match ?

[#1]

Hi all,

i have the following problem:

- I have calendar table covering the years 2011 to 2013 with one record per day

- I have a table "Budgets" with the following fields:

ID	START	END	AMOUNT
123	05-04-2012	11-30-2012	150.000,00
456	01-01-2012	12-31-2012	225.000,00

Now i want to every record of the table Budget up to get one record per workday in the period of START and END. Means, i neew something like this after the split:

ID	START	AMOUNT
123	05-04-2012	XX.XX
123	05-07-2012	XX.XX
123	05-08-2012	XX.XX

(XX.XX would of course be replaced by Amount/Number of weekdays in period..)

I think i first need to calculate the number of weekdays in the period. that i can do. But how would i get the table with one budget record per weekday ?

Thanks a lot for help!

[#2]

Interval match is not going to work in your sample as the ranges are overlapping.

I guess the only possible solution is to use some kind of loop.

TestTable:

LET vBudgetRows = NoOfRows('Budgets');

FOR i = 0 to $(vBudgetRows) - 1

    LET vBudgetID = Peek('ID', $(i), 'Budgets');

    LET vStart = Peek('START', $(i), 'Budgets');

    LET vEnd = Peek('END', $(i), 'Budgets');

    LOAD $(vBudgetID) as [ID], Date($(vStart) + RecNo()) as [Date] autogenerate $(vEnd) - $(vStart)

NEXT i

[johnw · Accepted solution]

With some debugging, the FOR loop would generate the dates in the range, but a WHILE loop is easier than a FOR loop:

LOAD ID,date(Start+iterno()-1) as Date
RESIDENT Budgets
WHILE Start+iterno()-1 <= End;

But we also only want to allocate the amount to weekdays, and we do need to actually allocate the amount.  One way to do it is like this:

AllocatedBudgets:
LOAD *
WHERE weekday(Date)<5
;
LOAD ID, date(Start+iterno()-1) as Date
RESIDENT Budgets
WHILE Start+iterno()-1 <= End
;
LEFT JOIN (Budgets)
LOAD ID, count(Date) as Weekdays
RESIDENT AllocatedBudgets
GROUP BY ID
;
LEFT JOIN (AllocatedBudgets)
LOAD ID, PeriodBudget/Weekdays as AllocatedBudget
RESIDENT Budgets
;

See attached.

Edit: You could use networkdays() to establish Weekdays during the initial load of the Budgets table, or use it as the join instead of count(Date).  I think the count(Date) approach is a little more robust, as you're guaranteed to have the same number, where the networkdays() to me leaves you a little more open to things being out of sync.  Also, if you want to add holidays, while you CAN do so with networkdays(), even loading the holidays from a table, it will I think be easier to do it with the count(Date) approach.  You just need an additional condition on the WHERE for not exists(Holiday,Date) once you've loaded in your Holidays.

[#4]

Mr. Witherspoon, thank you very much, this works perfectly.