Date Forecasting

[cdw0308]

I have a field called start_date and a field called duration. I also have a field called project_value.
What I am trying to accomplish is:
Insert into a table the value of the start date and the monthly increments up to the number that is the duration field.
Example: 5/1/2008 with duration of 3
Inserted value would be:
5/1/2008 into month1 field of table Forecast
6/1/2008 into month2 field of table Forecast
7/1/2008 into month3 field of table Forecast

The number of months inserted will not be more than 12months
I have fields month1 to month12 in my table Forecast. Duration field will change for each project.
Also I would need the project value to be inserted into the same table but divided by the duration number and split across each month.
Example: 75,000 with a duration of 3
Inserted value would be:
25,000 into month1value field of table Forecast
25,000 into month2value field of table Forecast
25,000 into month3value field of table Forecast

Project_ID is the unique identifer on the Forecast Table.
I just need a little help trying to put all of this together.
I appreciate any help. Thanks.

 

[FALCONSEYE]

Can i make a suggestion? Wouldn't be easier the do a startDate, endDate and then calculate the cost based on the dates in between?

http://www.fuzzysiberians.com

 

[cdw0308]

I am not sure if I understand.
I am not good with date calculations.
The end date is determined once i know the duration which can be anything between 1 and 12.
Then i need to have every month that falls into date range inserted into the table as well as a corresponding value which is project_value/duration.
Can you give me an example or help in getting the code started so I start in the right direction.

Thanks

 

[FALCONSEYE]

For example:

startDate: 5/12/2008
endDate : 6/12/2008.

So, the duration is one month and you will calculate the cost for that one month.
Do you have situations like the duration is not exactly x month?
startDate: 5/12/2008
endDate: 5/31/2008

http://www.fuzzysiberians.com

 

[cdw0308]

i need to be able to show a matrix of amounts along with there dates.
If I enter a project start date of 5/12/2008 and it has a duration of 3 months with a project value of 75,000
I need to populate a forecast table with
5/12/2008 to month1 field
6/12/2008 to month2 field
7/12/2008 to month3 field
also need 25,000 to month1value field
25,000 to month2value field
25,000 to month3value field.
With 25,000 being the projectvalue of 75,000 divided by the duration of 3.
Now the next project can be a differant duration and i will need it to calculate 8 months for example. Nothing over 12 months.
I need it to be put in a table so I can generate a report showing the totals of money expected by project per month.
I hope this help answer your duration question.
Thanks for helping.