July 29, 2009 at 7:14 am
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
July 29, 2009 at 7:41 am
Hello!
If you can provide some tables, sample data, queries you have already written, and the output as you want it to look, someone will be able to help. Sorry, without really seeing the problem I can't offer any help.
There's a good article on posting questions with necessary info at following url http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 30, 2009 at 5:42 am
Thanks for replying Alister.
I've attached a text file with that will create two tables; the budgets table and another table I've called a view, purely because in real life I've used more tables. There's sample data there as well.
The budgets table has a 'period' column that has the year as the first part and the month as the second part.
What I would like to do in an accurate manner is to show the amount spent to date from the start of the year i.e. 2009,
•The amount spent so far on the month the dataset (or report) has been queried
•What’s left to spend for that month, if anything; this means taking into account what’s been spent to date since the start of the year compared to the budget amount from the start of the year to the end of the month in question
•How much budget left to year end
If there is any existing examples or articles that someone can point to, I’d be grateful.
Cheers
July 30, 2009 at 10:29 am
First off, I would recommend to anyone posting sample data that includes dates to put the dates in 'yyyy-mm-dd' format. That format is unambiguous whereas the formats 'dd/mm/yyyy' and 'mm/dd/yyyy' are potentially ambiguous.
I think that part of the problem you are running into is that you are trying to join everything and then sum when you need to sum first and then join.
WITH Spending AS (
SELECT
CompanyID
, DivisionID
, Sum(CurNetValue) AS CurrSpending
FROM dbo.vwDivisionSpend
WHERE SpendDate < GetDate() -- May not be necessary
GROUP BY CompanyID, DivisionID
)
, BudgetYTD AS (
SELECT
CompanyID
, DivisionID
, Sum( CASE WHEN Period < Convert( varchar(6), GetDate(), 112) THEN CurBudget ELSE 0 END ) AS BudgetYTD
, Sum(CurBudget) AS TotalBudget
FROM dbo.tblBudgets
GROUP BY CompanyID, DivisionID
)
SELECT
Spending.CurrSpending
, BudgetYTD.BudgetYTD
, BudgetYTD.BudgetYTD - Spending.CurrSpending AS MonthlyAvailable
, BudgetYTD.TotalBudget
, BudgetYTD.TotalBudget - Spending.CurrSpending AS TotalAvailable
FROM BudgetYTD
LEFT OUTER JOIN Spending
ON BudgetYTD.CompanyID = Spending.CompanyID
AND BudgetYTD.DivisionID = Spending.DivisionID
I've made a simplifying assumption that monthly figures are only soft estimates and that any balance (positive or negative) will be brought forward to the following month. It's easy to alter the above to calculate the differences where this is not the case.
You may also want to use a parameter instead of the GetDate() in the above code.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2009 at 2:37 am
Thanks Drew for your help. I'm working on the code just now and will let you know how I get on.
Cheers
Paul
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply