SQL GROUP BY total cost of each week of the year

You will need a list of weeks from the first date to the last date. Also, you will need the ISO year as this may be different from the calendar year.

First, create a table Number

with one field: Number

. Make this your main index.

Fill this in with ten entries from 0 to 9.

Then create a query DateFirstLast

that will take the first and last date as parameters:

PARAMETERS 
    DateFirst DateTime, 
    DateLast DateTime;
SELECT 
    DateAdd("d",[Hundred]![Number]*100+[Ten]![Number]*10+[One].[Number],[DateFirst]) AS [Date]
FROM 
    [Number] AS One, 
    [Number] AS Ten, 
    [Number] AS Hundred
WHERE 
    DateAdd("d",[Hundred]![Number]*100+[Ten]![Number]*10+[One].[Number],[DateFirst])<=[DateLast];

      

        
        
        
      

    

Now, use this as a source in a new request to generate ISO year-year numbers:

SELECT 
    DateFirstLast.Date, 
    ISO_WeekYearNumber([Date]) AS YearWeek
FROM 
    DateFirstLast;

      

        
        
        
      

    

This list contains dates and year-week numbers for dates.

For example, in 2012 it will be from 2011W52

to 2013W01

.

Week number calculation function:

Public Function ISO_WeekYearNumber( _
  ByVal datDate As Date, _
  Optional ByRef intYear As Integer, _
  Optional ByRef bytWeek As Byte) _
  As String

' Calculates and returns year and week number for date datDate according to the ISO 8601:1988 standard.
' Optionally returns numeric year and week.
' 1998-2007, Gustav Brock, Cactus Data ApS, CPH.
' May be freely used and distributed.

  Const cbytFirstWeekOfAnyYear  As Byte = 1
  Const cbytLastWeekOfLeapYear  As Byte = 53
  Const cbytMonthJanuary        As Byte = 1
  Const cbytMonthDecember       As Byte = 12
  Const cstrSeparatorYearWeek   As String = "W"

  Dim bytMonth                  As Byte
  Dim bytISOThursday            As Byte
  Dim datLastDayOfYear          As Date

  intYear = Year(datDate)
  bytMonth = Month(datDate)
  bytWeek = DatePart("ww", datDate, vbMonday, vbFirstFourDays)

  If bytWeek = cbytLastWeekOfLeapYear Then
    bytISOThursday = Weekday(vbThursday, vbMonday)
    datLastDayOfYear = DateSerial(intYear, cbytMonthDecember, 31)
    If Weekday(datLastDayOfYear, vbMonday) >= bytISOThursday Then
      ' OK, week count of 53 is caused by leap year.
    Else
      ' Correct for Access97/2000+ bug.
      bytWeek = cbytFirstWeekOfAnyYear
    End If
  End If

  ' Adjust year where week number belongs to next or previous year.
  If bytMonth = cbytMonthJanuary Then
    If bytWeek >= cbytLastWeekOfLeapYear - 1 Then
      ' This is an early date of January belonging to the last week of the previous year.
      intYear = intYear - 1
    End If
  ElseIf bytMonth = cbytMonthDecember Then
    If bytWeek = cbytFirstWeekOfAnyYear Then
      ' This is a late date of December belonging to the first week of the next year.
      intYear = intYear + 1
    End If
  End If

  ISO_WeekYearNumber = CStr(intYear) & cstrSeparatorYearWeek & Format(bytWeek, "00")

End Function

      

        
        
        
      

    

List of dates and weeks that you can combine with your sales data and apply grouping as needed.