Generating Months, Quarters & Years between two dates

[#1]

Dear All,

I have a query regarding creation of period basis the start date and end dates.

ex: I have a contract starts from 20-Dec-2010 and ends 30-jun-2011, then i want to see the information as number of days for each month, qtr & year

Please help me in this asap.

[pover]

Just to make sure I understand correctly, do you want to generate the years (2010, 2011), months (December, January, February, March, April, May, June) and quarters (Q4, Q1, Q2) for filtering?

Or, do you want to know that the contract will last 0 years, 6 months or 2 quarters? If you want to know how long the contract will last, would your example last 2 quarters, 3 quarters or 2.1 quarters?

Regards.

[#3]

Firstly thank you for the reply Mr. Pover.

Yes I want to know the periods like Q1,Q2,Q3,Q4 and months and year between the contract period mentioned, so that I can show the data as number of days for that month/quarter/year as dimensions

[#4]

Hi there,

I use the following script to do that, don't know if it's the best around. Difference is that I just need dates for de last 4 years, you'll need to assign the variables vStartDate and vEndDate in another way:

(In your fact table, make sure you have the factdate loaded as 'Date' (or the same fieldname as you'll use in the calendar table).

Let vToday=num(Today());

Let vStartDate = num(MakeDate(Year(ToDay()) - 4, 1, 1));

Let vEndDate = num(MakeDate(Year(ToDay()), 12, 31));

//*************** Temporary Calendar ***************

TempCalendar:

LOAD

$(vStartDate)+IterNo()-1 as Num,

Date($(vStartDate)+IterNo()-1) as TempDate

AUTOGENERATE 1 WHILE $(vStartDate)+IterNo()-1<= $(vEndDate);

//*************** Master Calendar ******************

Calendar:

LOAD

TempDate as Date,

Year(TempDate) as Year,

Month(TempDate) as Month,

'Q' & Ceil(Month(TempDate)/3) as Quarter,

Week(TempDate) as Week

RESIDENT TempCalendar

ORDER BY TempDate Asc;

Regards,

Yves