How to model Week Commencing / Week Ending scenario

• s_osborne2

  SSCertifiable

  Points: 6726

  More actions

  January 31, 2013 at 6:00 am

  #288572

  I’m reasonably new to data warehousing and I’m struggling to get a feature of my time dimension working. I’d like to model a Week Commencing and Week Ending type of scenario.

  I’ve created a DimTime table as per below.

  DECLARE @startDate DATETIME

  SET @startDate = '2004-01-01'

  IF OBJECT_ID('tempdb..#TallyTable') IS NOT NULL DROP TABLE #TallyTable

  IF OBJECT_ID('tempdb..#CalendarTable') IS NOT NULL DROP TABLE #CalendarTable

  SELECT TOP 10000 identity(INT,1,1) AS N

  INTO #TallyTable

  FROM

  master.dbo.SysColumns c1

  CROSS JOIN master.dbo.SysColumns c2

  ALTER TABLE #TallyTable ADD CONSTRAINT PK_Tally_N

  PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

  SELECT

  [N] 'TimeKey',

  dateadd(dd, N-1, @startDate) 'LongDate',

  CONVERT(date,dateadd(dd, N-1, @startDate)) 'ShortDate',

  dateadd(YYYY,datediff(yy,0,dateadd(dd, N-1, @startDate)),0) 'YearKey',

  datepart(YYYY,dateadd(dd, N-1, @startDate)) 'YearNumber',

  dateadd(mm,datediff(mm,0,dateadd(dd, N-1, @startDate)),0)'MonthKey',

  datepart(mm,dateadd(dd, N-1, @startDate)) 'MonthNumber',

  datepart(dd,dateadd(dd, N-1, @startDate)) 'DayOfMonthNumber',

  datename(mm,dateadd(dd, N-1, @startDate)) 'MonthName',

  datename(dw,dateadd(dd, N-1, @startDate)) 'DayName',

  dateadd(qq,datediff(qq,0,dateadd(dd, N-1, @startDate)),0) 'QuarterKey',

  datepart(qq,dateadd(dd, N-1, @startDate)) 'QuarterNumber',

  dateadd(ww, DATEDIFF(ww,0,convert(date,dateadd(dd, N-2, @startDate))), 0) 'WeekCommencing',

  dateadd(dd,6,DATEADD(ww, DATEDIFF(ww,0,convert(date,dateadd(dd, N-2, @startDate))), 0)) 'WeekEnding'

  INTO

  #CalendarTable

  FROM

  #TallyTable

  WHERE

  dateadd(dd, N-1, @startDate) < '2024-01-01'

  ORDER BY

  N

  SELECT * FROM #CalendarTable ct

  So looking at TimeKey 1 you can see that the 1st of Jan 2004 fell into the week commencing 29/12/2003 and week ending 04/01/2004.

  For our purposes a week starts on a Monday and ends on a Sunday.

  The requirement is to have a number of named sets including:

  Current week - which given today’s date would be anything that falls between 28/01/2013 and 03/02/13.

  Previous week - which given today’s date would be anything that falls between 21/01/2013 and 27/01/13.

  Current year up to end of previous week - which given today’s date would be anything that falls between 01/01/2013 and 27/01/13. Currently my DimTime dimension in SSDT is as per screenshot DimTime.png

  My attribute relationships are defined as in AttribRel.png

  Is it possible to model a week commencing/ending type of scenario or am I trying to achieve something that isn’t possible? And if so how? I’ve tried a bit of research on google but not coming up with anything that solves this problem. Plenty of examples for “Week of year” type scenarios but nothing for week commencing.

  Thanks,

  Simon

  ---

  
  MCSE: Data Platform
  MCSE: Business Intelligence
  Follow me on Twitter: @WazzTheBadger
  LinkedIn Profile: Simon Osborne