Reformatting table based on historical data

[#1]

I've been trying to get to a solution for what I feel might be a common problem. My technical knowledge is a bit limited, so I might have missed the obvious..

What I have is a table with employees and certain values that are valid in periods, with a startdate and an enddate. A person might have multiple records in this table, for consecutive timeperiods: (date is dd-mm-yy)

EMPLOYEE_ID STARTDATE ENDDATE VALUE
101 01-06-2008 30-04-2009 1
101 01-05-2009 30-09-2009 0.9
101 01-10-2009 ... 0.95
The last record is in the current period, so it has no enddate. (We're talking about personnel FTE values btw..)

What I also have is an INLINE with Year/MonthNr for 2008, 01 until 2010,12.

What I want my endresult to be is a table like:

EMPLOYEE_ID YEAR MONTH VALUE
101 2009 3 1
101 2009 4 1
101 2009 5 0.9

I've only entered a small part, but every employee should have a record for each year and month in the mentioned period from 2008 until 2010, obviously with the correct Value placed in to it, based on the historical value table.

I've anyone could come up with useful suggestions, I'd be grateful.

[Oleg_Troyansky · Accepted solution]

I'd suggest using LOAD ... WHILE functionality. Something like the following:

LOAD
 EMPLOYEEID,
 date(MonthStart(FromDate) + IterNo() - 1, 'MMM-YYYY') as Month,
 Value
resident
 Details
WHILE
 MonthStart(FromDate) + IterNo() - 1 <= EndDate
;

[#3]

Your suggestion almost did the trick for me. Basically, I think this is a good way to solve my problem, so thanks a lot already!
In trying I ran into what I hope to be a minor problem.

When I try your suggestion, it comes up with the desired format, but every month is in my table approx 30 times over. (With the required Value by the way!). I only need that record one time obviously. I tried to put in a DISTINCT, but that didn't work out.

In my test table for Details I have 3 records with fields Employee, Startdate, EndDate and Value, like I stated, but there are more fields in reality. I'll pass them on to give you a complete overview. The first 5 fields are identical on each of the 3 records.

Employee_ID
Contractstartdate
CostCenter
FTE_number
ValueType (there are multiple values, but I've filtered on only one to make the mechanism work)
LogDate (date the value change was carried out)
LogMaand (month the value change was carried out)
LogYear (year the value change was carried out)
NewValue
OldValue

Any suggestions on how to solve this? (I did get the desired end table with a Details-1 name and solved that by renaming the fields for Employee_ID and Value so that it became a 'stand-alone' table.)

[#4]

I guess that it has something to do with the days and the months, since it is either 30 or 31 times. So I'll check on the date/month function and I think it will be alright then.

[Oleg_Troyansky]

Based on your description, it looks like you have daily records in your table, and that values can change on the daily basis... It might be a bit more complex (I'm not sure how would you like to treat the changing values).

Load distinct should help get rid of the repetitive values. You might have to first load distinct Start Date and End Date for each contract, and only them use LOAD ... WHILE. The Value and how to treat it is another story...

[#6]

I don't think it's more complex, since whilst changes can be made on a daily basis, the reality is that all changes are for multi month periods. So we can use the month principle. I think I can just take your example and convert to year-month records instead of daily dates and I assume the WHILE thing can then be applied to a month in stead of a date? At least I'll be trying that. If I'm wrong here, please let me know.

[#7]

I got it to work using the handed principle but altering it for an AddMonths(FromDate, IterNo()-1). Thanks a lot!

[Oleg_Troyansky]

Ah, right!!! I totally forgot - AddMonths it is! Sorry about it!