CODEWITHSUNDEEP
sqlsql-server-2008
Gabe
Gabe

Reputation: 6055

How to create list of weeks corresponding to a month in SQL?

I need to create a report that looks like the following ("WE" is week-ending Sun to Sat):

SEPT.           
            WE 9/10     WE 9/17     WE 9/24     WE 10/1
Metric#1    40          49          58          40
Metric#2    24          25          20          1

The above may not be correct, it's just an example of the format.

My issue is I don't know which weeks correspond to which months. For example, the week of Oct 30 to Nov 5, I don't know which month it belongs to.

They've given me flexibility on how to do this. As long as it's consistent, it should ok.

I could also use the month of that week's Sunday (oct 30- nov-5 would be an October week because Oct 30 is a Sunday).

Could use some help here on figuring out how to assign a week to a particular month. I can do the pivoting, it's the date logic that I'm not sure about. The report is for the past 3 months.

SQL Server 2008. Report will be in SSRS 2008 R2

edit: Just got the requirements from the client. The month that has the most number of days in that week. For example Aug 28 to sept 3, for that week there are 4 days in August and 3 days in Sept, so that week belongs in August. I'll see if I can work this out.

Upvotes: 1

Views: 2157

Answers (3)

Andriy M
Andriy M

Reputation: 77657

You can use dates of Wednesdays to determine which months the corresponding weeks belong to. Your query might look something like this:

WITH marked AS (
  SELECT
    *,
    WednesdayDate = DATEADD(DAY, 4 - DATEPART(WEEKDAY, DateColumn), DateColumn)
  FROM atable
),
grouped AS (
  SELECT
    WeekYear   = YEAR(WednesdayDate),
    WeekMonth  = MONTH(WednesdayDate),
    WeekEnding = DATEADD(DAY, 3, WednesdayDate),
    Metric1    = AGG(...),
    Metric2    = AGG(...),
    ...
  FROM marked
  GROUP BY WednesdayDate
)
SELECT *
FROM grouped
WHERE WeekYear  = ...
  AND WeekMonth = ...

Your next step would probably be dynamic pivoting.

Upvotes: 2

Jamie F
Jamie F

Reputation: 23789

Your additional requirement means that the month of the week will always be the month of the week's Wednesday (since you have Sun-Sat weeks.)

So something like this will get you the month for any date:

DECLARE @TestDate DATETIME

SET @TestDate = 'October 31, 2011'

SELECT 
   DATEADD(week, DATEDIFF(week, 0, @TestDate), 2) AS WednesdayOfWeek, -- This is Wednesday of the week.
   DATEPART(MONTH, DATEADD(week, DATEDIFF(week, 0, @TestDate), 2)) AS MonthNum,
   DATENAME(MONTH, DATEADD(week, DATEDIFF(week, 0, @TestDate), 2)) AS MonthName,
   DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(week, DATEDIFF(week, 0, @TestDate), 2)), 0) AS FirstOfMonth

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

A problem like this is best solved with a calendar table: http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Basically, you build a table of days (10 years of days is only 3652 rows), and you associate each date with the week you want to assign it.

Upvotes: 2

Related Questions