Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2015
    Posts
    1

    Unanswered: Year to date figures

    I would like to get year to date figures for budgeted sales. I have monthly figures for each month and, for example, on Monday I need to calculate year to date budgeted sales ending with 4th week of fiscal period.
    Here is the code that I created so far:

    use database1
    GO
    CREATE PROCEDURE spGetBudgetSales
    @fy int,
    @fp int,
    AS
    BEGIN
    SELECT
    SO.FiscalYear,
    SO.FiscalPeriod,
    SUM(BS.Budget) AS "TOTAL"
    FROM BudgetSales BS
    WHERE BS.FiscalYear = @fy
    AND BS.FiscalPeriod = @fp
    GROUP BY BS.FiscalYear,
    BS.FiscalPeriod,
    ORDER BY BS.FiscalPeriod

    END

    So, I have two whole months behind and partial extraction of 4 out of 5 weeks in this month (March). I need to sum these two full months and partial amounts of 4 weeks. Next week would be all three months summed and then incrementing into 4th period first week and so on. What would be the best way to accomplish this?
    Thank you.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Just like almost any database should have a table with numbers (check with Itzik Ben Gan if in doubt), it would not be a bad idea to equip every database where "date/time" values are used for any purpose with a Calendar table. Such table should have (mine does) the following columns:

    Code:
    CalendarDayChar
    CalendarMonthChar
    CalendarYearChar
    CalendarDateChar
    CalendarDay
    CalendarMonth
    CalendarYear
    WeekOfTheYear
    DayOfTheYear
    DayOfTheWeek
    LeapYear
    CalendarDate
    CalendarDateEnd
    DayOfWeekOccurrence
    CalendarWeekStartDate
    CalendarWeekEndDate
    WorkWeekStartDate
    WorkWeekEndDate
    DSTStart
    DSTEnd
    DSTInEffect
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •