Code for 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 2005 Forums

Transact-SQL (2005)

Code for budgets

Author

Topic

cidr
Posting Yak Master

207 Posts

Posted - 2009-07-29 : 08:28:01

Hi there,

I'm having a little trouble creating a dataset that will show where a company is in terms of the spend in comparison to their budget.

the company is split into division and each division is given a budget to spend. The budgets are split into monthly budgets.

I've created a budgets table and the budget is split into monthly budgets for each area of the company. Each time a division spends money on repairs, this will be recorded in a table with a date the amount was approved.

I'd like the dataset to show how much each division has spent in total to date and for that month (which I've pretty much done). In regards to the budgets, I'd like to show the budget amount they have left for the year (summing the budget as a whole) and how much budget they have left for that month.

I'm not really sure where to start. If anyone could help me at all I'd be very grateful. Even if pointing me to similar articles with existing code would help.

many thanks

p

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 08:39:07

declare @DivisionBudgets table
(
DivisionId int,
AllocatedMonth datetime,
Amount Money
)
insert into @DivisionBudgets
select 1,'1/1/2009',1000 union all
select 2,'1/1/2009',500 union all
select 1,'1/2/2009',1200 union all
select 2,'1/2/2009',700
declare @Expenditures table
(
DivisionId int,
ExpeditureDAte datetime,
Amount money
)
insert into @Expenditures
select 1,'1/10/2009',100 union all
select 2,'1/15/2009',150 union all
select 1,'2/10/2009',300 union all
select 2,'2/10/2009',100 union all
select 1,'2/15/2009',100 union all
select 1,'2/25/2009',100

; with cte as
(
select D.DivisionId,D.AllocatedMonth,D.Amount as Budget,E.Amount as Expenditure
from @DivisionBudgets D
inner join @Expenditures E on E.ExpeditureDAte >= D.AllocatedMonth and E.ExpeditureDAte < Dateadd(MM,1,AllocatedMonth) )
select DivisionId,AllocatedMonth,Budget,sum(Expenditure) as Spent,Budget - sum(Expenditure) as Remaining
from cte group by DivisionId,AllocatedMonth,Budget

cidr
Posting Yak Master

207 Posts

Posted - 2009-08-11 : 05:08:48

Hi ddramireddy, thanks for getting back to me.

I managed to sort this problem out. However, the code I used is rather convoluted, and long.

If you want me to show you what I used. Please let me know.

Thanks again for your help.

Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources