Spreading Dollars across fiscal period

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Spreading Dollars across fiscal period

Author

Topic

bwebb
Starting Member

4 Posts

Posted - 2007-07-19 : 14:30:13

I have select statement that returns data on a construction project.
I have a start date, end date, and forecasted cost for each task in the project. I need to create a table that spreads the dollars in a linear fashion broken down by fiscal period.

I have:

task_id, start_date, end_date, cost
1, 9/15/2008, 12/15/2008, 3000
2, 7/1/2008, 12/15/2008, 550

I need

task_id, fiscal_period, cost
1, 200803, 500
1, 200804, 1000
1, 200805, 1000
1, 200806, 500
2, 200801, 100
2, 200802, 100
2, 200803, 100
2, 200804, 100
2, 200805, 100
2, 200806, 50

I can do the math to properly calculate the dollar amount, I am having trouble creating the statement that will process through each row of my select statement and insert multiple rows into the new table.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-19 : 14:48:21

How did you get 200803, 200804, 200805 from "9/15/2008, 12/15/2008"?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

bwebb
Starting Member

4 Posts

Posted - 2007-07-19 : 22:53:51

Our fiscal year begins in July, so today, 7/19/2007, falls in Fiscal Period 200801

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-19 : 22:57:17

please also explain how to get
1, 9/15/2008, 12/15/2008, 3000
into
1, 200803, 500
1, 200804, 1000
1, 200805, 1000
1, 200806, 500

You have to explain your business logic to us. You can't assume everybody understand is in the same industry as you.

KH
[spoiler]Time is always against us[/spoiler]

bwebb
Starting Member

4 Posts

Posted - 2007-07-19 : 23:35:01

I'm sorry, I have a few typo's also.. We have construction schedules for a School District that has a Fiscal Year from July to June. The 'powers to be' would like to know the forecasted amount that they will spend in the upcoming months and fiscal year so that they can sell bonds at an appropriate time. The table I listed above should read:
task_id, fiscal_period, cost
1, 200903, 500
1, 200904, 1000
1, 200905, 1000
1, 200906, 500
2, 200901, 100
2, 200902, 100
2, 200903, 100
2, 200904, 100
2, 200905, 100
2, 200906, 50

The first task takes place in the Fiscal year of 2009 since we use the Year the fiscal year ends in as the Fiscal Year. Hence 6/1/2009 is in the Final month of Fiscal Year 2009 and therefore is Fiscal Period 200912. July 08 is 200901, August 08 is 200902, Sept 08 is 200903 etc until June is 200912. So the first task listed above is from 9/15/2008 to 12/15/2008. If you break down the $$ to be spent during those months in a linear fashion you would spend $500 in Sept due to 15 days, $1000 in Oct and November because they are full months, and $500 in December since it is only half a month too.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources