SQL HELP? - SQL Server Forums

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

SQL HELP?

Author

Topic

TheWomanInIT
Starting Member

3 Posts

Posted - 2011-05-24 : 05:44:25

[font=Tahoma]I am creating a database for managing budgets.

A Department gets a certain amount of budget each year and chooses how they want it to be split eg: IT get £8,000 and want to split it over 8 periods - £1000 per period.

The problem i am having is that i need to create a new row in a new table for each split. So I need it to check the field "number of splits" and create that amount of periods eg: Row 1 - number of splits = 8, 8 new rows are created with the budget for each of the months (£1000) also stored on that row. And if row 2 = 2 splits £8,000 is divided by 2, two new rows are created and £4000 is stored in each. so i should get something that looks like this

ID Period Number Of Splits Budget this period

1 1 8 £1000
1 2 8 £1000
1 3 8 £1000
1 4 8 £1000
1 5 8 £1000
1 6 8 £1000
1 7 8 £1000
1 8 8 £1000

2 1 2 £1000
2 2 2 £1000

I need this to be flexible in case someone spends over/under one month.. but getting the above sorted is my priority.

Any help for a beginner?[/font=Tahoma]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-24 : 06:18:34

use a number table or tally table. If you don't have one, you can also use F_TABLE_NUMBER_RANGE


declare	@splits	int,
	@amt	decimal(20,2)

select	@splits	= 7,
	@amt	= 8000

select	*, 
	amt 	= case 	when number <> @splits
			then convert(decimal(10,2), @amt / @splits)
			else @amt - convert(decimal(10,2), @amt / @splits) * (@splits - 1)
			end
from	numbers
where	number	between 1 and @splits

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

TheWomanInIT
Starting Member

3 Posts

Posted - 2011-05-24 : 06:31:45

wow... in process of doing now!! thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources