deriving calendar month from week number

Question

Hi Guys
I've had a hunt around for something similar to this but can't find anything.

I have a query that provides the number of transactions that have occurred each day and need to group by year, month, week BUT of course some months span multiple week numbers, eg. Sept. & Oct. 2009.

Take for example week 39 last year (September & October). Thursday is the 1st October therefore 4 days of that week fall in Oct., therefore the volume of transactions for the last 3 days of Sept. should be added to the first week of October's totals? Clear?

For example:

VOLUME----TRANSACTION----YEAR----MONTH----WEEK

1264.1730----53----2009----September----37
2739.7200---109----2009----September----38
522.5500-----21----2009----October----39
1196.6450----51----2009----September----39
2827.9550---113----2009----October----40
2730.4050---110----2009----October----41
3763.7200---154----2009----October----42
3425.6250---137----2009----October----43
3551.8100---143----2009----November--44
2788.0150---113----2009----November--45

Answer 1

The problem is that the calendar is awkward, and there's not much you can do about it. As far as I can see, you have three choices:

1. Group by year and month. Display the week or weeks in the result but don't group by them.
2. Group by year and weeks. Display the month or months in the result but don't group by them.
3. Group by year, month, week, and accept that some of the groups contain less than one week's data. (i.e. what you have now)

From your description it seems like you want option 2:

SELECT year, MIN(month), week, SUM(transaction)
FROM Table1
GROUP BY year, week

Answer 2

Something like this would do:

-- For weeks starting Sunday and ending Saturday, the US default:
SET DATEFIRST 7 

-- Alternatively, for weeks starting Saturday and ending Friday:
--SET DATEFIRST 6 

SELECT 
  [Date]
, DATENAME(WEEKDAY,[Date]) AS [DayOfWeek]
, DATEADD(DAY,1-DATEPART(WEEKDAY,[Date]),[Date]) AS WeekStarting
, DATEADD(DAY,7-DATEPART(WEEKDAY,[Date]),[Date]) AS WeekEnding
FROM (
  SELECT CONVERT(DATETIME,'20100124') UNION ALL
  SELECT CONVERT(DATETIME,'20100125') UNION ALL
  SELECT CONVERT(DATETIME,'20100126') UNION ALL
  SELECT CONVERT(DATETIME,'20100127') UNION ALL
  SELECT CONVERT(DATETIME,'20100128') UNION ALL
  SELECT CONVERT(DATETIME,'20100129') UNION ALL
  SELECT CONVERT(DATETIME,'20100130') UNION ALL
  SELECT CONVERT(DATETIME,'20100131') UNION ALL
  SELECT CONVERT(DATETIME,'20100201') UNION ALL
  SELECT CONVERT(DATETIME,'20100202') UNION ALL
  SELECT CONVERT(DATETIME,'20100203') UNION ALL
  SELECT CONVERT(DATETIME,'20100204') UNION ALL
  SELECT CONVERT(DATETIME,'20100205') UNION ALL
  SELECT CONVERT(DATETIME,'20100206')
) a ([Date])

Then, convert your week start or end date to a month:

SELECT *
, WeekStartingMonthStart = DATEADD(DAY,1-DAY(WeekStarting),WeekStarting)
, WeekStartingMonthEnd   = DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(WeekStarting),WeekStarting)))
, WeekEndingMonthStart   = DATEADD(DAY,1-DAY(WeekEnding),WeekEnding)
, WeekEndingMonthEnd     = DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(WeekEnding),WeekEnding)))
FROM (
  SELECT 
    [Date]
  , DATENAME(WEEKDAY,[Date]) AS [DayOfWeek]
  , DATEADD(DAY,1-DATEPART(WEEKDAY,[Date]),[Date]) AS WeekStarting
  , DATEADD(DAY,7-DATEPART(WEEKDAY,[Date]),[Date]) AS WeekEnding
  FROM (
    SELECT CONVERT(DATETIME,'20100124') UNION ALL
    SELECT CONVERT(DATETIME,'20100125') UNION ALL
    SELECT CONVERT(DATETIME,'20100126') UNION ALL
    SELECT CONVERT(DATETIME,'20100127') UNION ALL
    SELECT CONVERT(DATETIME,'20100128') UNION ALL
    SELECT CONVERT(DATETIME,'20100129') UNION ALL
    SELECT CONVERT(DATETIME,'20100130') UNION ALL
    SELECT CONVERT(DATETIME,'20100131') UNION ALL
    SELECT CONVERT(DATETIME,'20100201') UNION ALL
    SELECT CONVERT(DATETIME,'20100202') UNION ALL
    SELECT CONVERT(DATETIME,'20100203') UNION ALL
    SELECT CONVERT(DATETIME,'20100204') UNION ALL
    SELECT CONVERT(DATETIME,'20100205') UNION ALL
    SELECT CONVERT(DATETIME,'20100206')
  ) a ([Date])
) a