Thread: Year to date figures
03-26-15, 11:17 #1Registered User
- Join Date
- Mar 2015
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:
CREATE PROCEDURE spGetBudgetSales
SUM(BS.Budget) AS "TOTAL"
FROM BudgetSales BS
WHERE BS.FiscalYear = @fy
AND BS.FiscalPeriod = @fp
GROUP BY BS.FiscalYear,
ORDER BY BS.FiscalPeriod
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?
03-26-15, 19:04 #2Registered User
- Join Date
- Jul 2003
- San Antonio, TX
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:
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."