In a large project management application, there is a category of resources which are managed in a special way. During the planning phase you have an activity with a planned start date and a planned finish date and a monthly sequence of estimated effort values for one or more resources. A period always refers to a real calendar month with period 1 being the month of the planned start date. But the first period and last period may not use up a full month.

Activity Table

TaskID Planned_Start Planned_Finish
------ ------------- --------------
1      2012-01-19    2012-03-09

Effort Table

  
TaskID ResID Period Amount
------ ----- ------ ------
1      1     1      75.0
1      1     2      140.0
1      1     3      50.0
1      1     4      60.0
1      2     2      30.0
1      2     3      10.0

When the activity is completed these monthly effort values are transformed into weekly actual values.

You first need to handle situations of there being monthly periods beyond the planned finish date. All the effort amounts in the monthly periods beyond the last period must be added into the period corresponding to the planned finish date.

You must then split the monthly values into weekly values. Each weekly value is calculated in proportion to the number of days of the month in which it resides. A weekly value may need to be generated twice if it crosses the boundary betweeen two months. In that case each weekly value must be proportional to the number of days the week occupies in each month. A week is assumed to start on a Monday and finish on a Sunday with all days being working days. In the output the weekdate must correspond to the Sunday of that week and the monthdate must have the day set to 1.

TaskID ResID WeekDate   MonthDate  Amount
------ ----- ---------- ---------- ------
1      1     2012-01-22 2012-01-01 23.1
1      1     2012-01-29 2012-01-01 40.4
1      1     2012-02-05 2012-01-01 11.5
1      1     2012-02-05 2012-02-01 24.1
1      1     2012-02-12 2012-02-01 33.8
1      1     2012-02-19 2012-02-01 33.8
1      1     2012-02-26 2012-02-01 33.8
1      1     2012-03-04 2012-02-01 14.5
1      1     2012-03-04 2012-03-01 48.9
1      1     2012-03-11 2012-03-01 61.1
1      2     2012-02-05 2012-02-01 5.2
1      2     2012-02-12 2012-02-01 7.2
1      2     2012-02-19 2012-02-01 7.2
1      2     2012-02-26 2012-02-01 7.2
1      2     2012-03-04 2012-02-01 3.1
1      2     2012-03-04 2012-03-01 4.4
1      2     2012-03-11 2012-03-01 5.6

1. The output should be ordered by TaskID, ResID, WeekDate, MonthDate.
2. The final amount of effort value must be rounded to one decimal place.
3. You can assume LANGUAGE to be set to us_english.
4. There can be gaps in the monthly period sequence.

Use the TSQL Script given below to generate the source table and fill them with sample data.

    
IF OBJECT_ID('TC77_Activity','U') IS NOT NULL
  DROP TABLE TC77_Activity
GO

CREATE TABLE TC77_Activity(
	TaskID INT,
	Planned_Start DATE,
	Planned_Finish DATE
)
GO
INSERT INTO TC77_Activity(TaskID,Planned_Start,Planned_Finish)
SELECT 1,'2012-01-19','2012-03-09'

SELECT * FROM TC77_Activity
GO

IF OBJECT_ID('TC77_Effort','U') IS NOT NULL
  DROP TABLE TC77_Effort
GO

CREATE TABLE TC77_Effort(
	TaskID INT,
	ResID INT,
	Period INT,
	Amount FLOAT
)
GO

INSERT INTO TC77_Effort(TaskID,ResID,Period,Amount)
SELECT 1,1,1,75.0 UNION ALL
SELECT 1,1,2,140.0 UNION ALL
SELECT 1,1,3,50.0 UNION ALL
SELECT 1,1,4,60.0 UNION ALL
SELECT 1,2,2,30.0 UNION ALL
SELECT 1,2,3,10.0

SELECT * FROM TC77_Effort
GO

1. The solution should be a single query that starts with a "SELECT" or “;WITH”

1. Read the Submission Guidelines and make sure that your solution follows them.
2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
3. Your solution can use features from all versions up to and including SQL Server 2012.