Adding Budgets - 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 2000 Forums

Transact-SQL (2000)

Adding Budgets

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2010-01-26 : 05:12:03

Hi there,

Recently I created a budget report in SSRS. The budget report is for a Fashion brand. The brand has 77 areas and each area has a budget.

My task is to take each area budget and divide it by 12 and add that divided amt to each month of the fiscal year (Starting Feb 1st - 31 Jan). There obviously other fields apart from the budget for each month and the dates; there's also year and areaID fields as well.

Usually I'd use some code I got to add dates to a calendar table similar to
DATEADD(d,((N-1)*7),@StartDate) 'datStarting'

However, I was wondering if anyone had experience with this type of thing and could help me? I obviously want to be able to make this task as manual as possible. for example, being able to divide the budget and adding it to each of the months for that year, for that Area.

Any help would be appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 05:15:23

isnt it a matter of just group by areaid and take sum(budget)/12.0

cidr
Posting Yak Master

207 Posts

Posted - 2010-01-26 : 05:41:23

Hi visakh, you're right, it is just a matter of dividing the sum.

I was thinking about re-writing the post. If i'm given a spread sheet (which I'd import to tblBudgetAmts) with all area budgets, I can divide that number by 12 and use that number to populate the budget table (with the 12 months) where the AreaID is equal.

However, I'd also like to find a way to run through each row within tblBudgetAmts, pick up each of the Budgets and do the same process for each of the 77 Areas.

For example, If there was a way to get the budget from tblBudgetAmts from the first AreaID with a specific year, divide it by 12, add that to the 12 rows of my Actul Budgets table and then automatically grab the second budget from the second AreaID where the year matches to do the process again. That would be helpful as there are 77 Areas.

The Budgets table will have an AreaID as well so perhaps there's a way to run through all the AReaIDs from tblBudgetAmts to populate my budget table as well.

Hope that makes sense.

:)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 06:01:55

you could just do


SEELECT areaID,DATEADD(yy,DATEDIFF(yy,0,datefield),0),SUM(Budget)
FROM table
GROUP BY areaID,DATEADD(yy,DATEDIFF(yy,0,datefield),0)

cidr
Posting Yak Master

207 Posts

Posted - 2010-01-26 : 09:55:58

Phew, I managed to do this with a While loop
I added budgets (with the AreaID) to a temp budget table and used WHILE to select each records values (including budget / 12) into variables and then updated the actual budgets table with the values.

Thanks again for all your help :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 10:38:52

quote:
Originally posted by cidr

Phew, I managed to do this with a While loop
I added budgets (with the AreaID) to a temp budget table and used WHILE to select each records values (including budget / 12) into variables and then updated the actual budgets table with the values.

Thanks again for all your help :)

while loop? why do you want loop for this? i dont think you need to do row by row processing here. you could just go for set based solution using GROUP BY

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-26 : 10:50:39

quote:
Originally posted by cidr
I obviously want to be able to make this task as manual as possible.

Have you just got off the Thailand Express?

http://www.sing365.com/music/lyric.nsf/A-Passage-To-Bangkok-lyrics-Rush/756F9B8BC905A8C848256BBF0031D4BC

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources