Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

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

Time Dimension

My attribute relationships are defined as in AttribRel.png

Attribute Relationships

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

share|improve this question
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.