Post reply

Advice on Budget code

  • November 6, 2009 at 4:37 am

    #71882

    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 (budget amount for the whole month is £1000)) 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 for 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 to

    Note: @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(getdate())

    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

  • November 6, 2009 at 9:05 pm

    #1076218

    Hi,

    Is any reason for the first month (i.e. @YearStartdate)treated as the day one ie day(@YearStartdate) always = 1, it is correct?

    However unless full tables schema and sample data its not easily understand.

    And your posted codes are correct, than just try this code.

    sum(case when (cast(YEAR(getdate())as varchar)+ cast(month(getdate())as varchar)) = b.PERIOD

    then ( (b.curBudget * ((day(getdate())- (case when month(@YearStartdate) = month(getdate())

    then day(@YearStartdate)+1 else 0 end)))/@EndMonthDays)

    else 0 END)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply