Tuesday, November 3, 2009

Extracting the Day of Week to roll up cohorts into Discrete Weeks

Dates seem to be a common theme for me.  I'm sure I'm not alone. Today I'm working on a project where I need to group all people into a calendar week for their event. Thus, I want to take their Event Date, extract AND subtract the DayOfWeek from the Event Date to group everyone by the week's first day.

Also, we calculate our weeks as Monday - Sunday, so you'll see a +2 on the day_of_week field.

SELECT 
StartDt
, EXTRACT(DAY from StartDt) as DayOfMonth
, day_of_week + 2 as DayOfWeek
, StartDt - (day_of_week + 2) as FirstDayOfWeek
from mydb.table
INNER JOIN sys_calendar.calendar
ON StartDt = calendar_date;

This give me:

SubStartDt DayOfMonth DayOfWeek FirstDayOfWeek
 09/25/2009         25         8 09/21/2009
 10/12/2009         12         4 10/12/2009
 09/28/2009         28         4 09/28/2009
 10/17/2009         17         9 10/12/2009
 09/29/2009         29         5 09/28/2009
 10/15/2009         15         7 10/12/2009
 10/07/2009          7         6 10/05/2009
 10/16/2009         16         8 10/12/2009
 10/11/2009         11         3 10/12/2009
 10/04/2009          4         3 10/05/2009



Now back to my weekly assessment.