Hi folks,I'd like to ask advice on the code below.I've created a budget report and for each month there is an allocated budget amount (i.e. January £1000, February £1000)The code below is part of what calculates how much budget there is on the date the report is run. The code below is called EndValue because it only calculates the month the report is run as it may not be a full months budget.There is a budget table Called Budgets that has a column called PERIOD (i.e. 200901, 200902... (2009 being the year 01 being the month))The code below is basically saying, when the month (November is the same in the Budget table (November) then calculate the budget amount from the start of that month to the date the report is run. This means that if the budget is divided by it’s days of the month into a daily budget, and the budget is run today, I want to know how much budget has been accumulated until now that this month.The code below isn’t mine but I’m sure the calculation’s not correct.If someone can have a look and let me know if the code actually does what it’s supposed toNote: @EndMonthDays = 30 days (November). It’s the ELSE statement that will be used here as it’s not the first budget month (i.e. @YearStartdate)Many thanks for any help,SUM(CASE WHEN month(getdate())=RIGHT(b.PERIOD, 2) AND LEFT(PERIOD, 4)= YEAR(@EndDate) THEN b.curBudget * (CASE WHEN month(@YearStartdate) = month(getdate()) THEN (day(getdate()) -day(@YearStartdate)+1) ELSE day(getdate()) END) /@EndMonthDays ELSE 0 END) AS EndValue
advice on Calculation
Posted - 2009-11-06 : 06:33:44
