All the effort amounts in the monthly periods beyond the last period must be added into the period corresponding to the planned finish date.

There can be gaps in the monthly period sequence.

truncate table TC77_Activity
truncate table TC77_Effort

INSERT INTO TC77_Activity(TaskID,Planned_Start,Planned_Finish)
SELECT 1,'2012-02-19','2012-04-09'

INSERT INTO TC77_Effort(TaskID,ResID,Period,Amount)
SELECT 1,1,1,100.0 UNION ALL
SELECT 1,1,2,100.0 UNION ALL
SELECT 1,1,5,100.0

I assume that period 5 must be added to april (not included in TC77_Effort), not to march (period 2).

That's correct. The effort past the last period must go into the last period, whether that period has data or not. Thus you should get:

1   1	2012-02-19	2012-02-01	9.1
1   1	2012-02-26	2012-02-01	63.6
1   1	2012-03-04	2012-02-01	27.3
1   1	2012-03-04	2012-03-01	12.9
1   1	2012-03-11	2012-03-01	22.6
1   1	2012-03-18	2012-03-01	22.6
1   1	2012-03-25	2012-03-01	22.6
1   1	2012-04-01	2012-03-01	19.4
1   1	2012-04-01	2012-04-01	11.1
1   1	2012-04-08	2012-04-01	77.8
1   1	2012-04-15	2012-04-01	11.1

How about efforts before the planned_start ?

There can be effort before the planned start - in a certain sense. This can happen for the week in which the planned start falls. But you should calculate the actual weighted by the number of days in that first week on or after the planned start date. For example, if the planned start is 2012-04-06 and the planned finish is 2012-05-25 then the first week is the week ending 2012-04-08 and only 3 days are involved. In this case, ther first period occupies 25 days and thus you must allocate 3/25 of the monthly effort as an actual to that first week. The same reasoning must be applied to the planned finish date.

Thus the first week goes from 2012-04-02 to 2012-04-08 and it makes it look like there are actuals before the planned start date. But we weighted the actual to compensate for this.

An even more special case is when the planned start and planned finish fall in the same month. For example, 2012-04-06 to 2012-04-20. This means the number of days in that month are only 15 and the first week gets 3/15, the second week gets 7/15 and the third week gets 5/15 of the monthly effort amount.

So if the plannedstart is 2012-04-06 and the plannedfinish is 2012-05-25 then there can be no effort in period 3 - and if there is, we can just skip this?

And another question. Can Activities be in more than 1 year? If so how should a case with an activity with a plannedstart 2012-01-01 and a plannedfinish 2013-01-31 handle efforts is period 1?

Yes, there could be a monthly effort amount in period 3 (June). And your job is to put it into period 2 (May) and then do the weekly distribution.

I don't understand your second question. Where exactly is the problem with an interval that's longer than 1 year? A period always refers to a month. Period 1 would be Jan-2012. Period 13 would be Jan-2013. There is no difference in handling such a case compared with one where the interval is less than a year.

Thanks for you answer. The reason for my question was that when I read the assignment, I only read "A period always refers to a real calendar month ..." and skipped this part "... with period 1 being the month of the planned start date".

I will make a small change in the submission.

In the original sample data, how come Amount in the expected result is greater than the Amount in the Effort table? Bug?

11.1 1 2012-03-04 2012-03-01 48.9 12.1 1 2012-03-11 2012-03-01 61.1

Amount for Period 3 (March) is 50, but the expected result adds up to 110.

The challenge description states that any amounts that go beyond the last period must be placed into the last period. Thus 60 in period 4 is placed in period 3.