Custom time period - 26th of month to 25 of next month

How do I create a server time dimension which has custom defined month periods.

For example we use month that starts on 26 and ends on 25 next month.

So our January is actualy starting from 26.Dec.2006 until 25.Jan.2007,

and so on for Feb, Mar...

Any idea how I can do it?

Thank you,

Mitja

Hello! Create a new column in your time dimension called CustomMonth.

Update TimeDim

Set CustomMonth = 'Jan'

Where CalendarDate between '2006-12-26' and '2007-12-25'

--

Continue with the next month.

This is a very simple solution. It possible to make it generic but that will require more thinking.

HTH

Thomas Ivarsson

|||

Thank you Thomas!

I have tried the proposed solution,

but I get syntax error at "where" critieria.

I was looking for some pre-made solution in business intelligence wizards,

strangely enough to find there isn't one.

Any ideas?

|||

This is an example that works with the AdventureWorksDW sample database:

Alter Table DimTime

Add SpecialMonth Char(3)

Select * from DimTime

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25' --365 Check the no of records that will be updated

-

Update DimTime

Set SpecialMonth = 'Jan'

where FulldateAlternateKey Between '2002-12-26' and '2003-12-25'

-

It is possible to build a full generic solution but it will take some time. You can use TSQL CASE for that.

This will help you to get started.

Regards

Thomas Ivarsson