Calculate days per month between two dates

[mwiggum]

Hi

I have following problem: There are two dates in the fact per registration, Start date & End date.
Now I have to calculate for any given year the total days spent in hotel per month.

Example:
Start date 01/15/2011
End date 02/14/2011

So for this registration I should show
January 16
February 14

Perhaps this is very simple and I just can't figure it out but I out of ideas at the moment. I searched this forum and couldn't find similar problem, so if there are then apologizes for not finding it.

thanks in advance

[Mark56]

I do think that this is possible but you need to consider a little more information to create the proper calculation.  What would be maximum difference between the start & end dates?  Two months, six months, two years, etc.

Cognos has a fiction of _days_to_end_of_month (C8.4).  You can calculate that for the Start & End dates. 

You can use extract(month, [Start]) = extract(month, [End]) to see if they are the same months.
 
The tricky part is determining the time in between if greater then two months. 

[Lynn]

You can do it with two queries and some date calculations.

One query would include date dimension information:
Period, StartDate, EndDate, NumberOfDays
201101  Jan 1 2011  Jan31 2011  31
201102  Feb 1 2011  Feb282011  28
etc.

Your fact query includes the start and end dates for each registration.

Join the two queries so that you get one row from your date dimension returned for each date within the fact start and end date range:

Join Expression:

Code Select Expand

( [Periods].[Period Start Date] between [FactData].[StartDate] and [FactData].[EndDate] )
or
( [Periods].[Period End Date] between [FactData].[StartDate] and [FactData].[EndDate] )

In the query resulting from the join, include a data item to calculate days for the partial beginning of a month, the partial ending of a month, and the days fully within the range.

BeginRange data item:

Code Select Expand

if ( _days_between([FactData].[StartDate], [Periods].[Period Start Date]) >= 0 )
then
( _days_between([FactData].[StartDate], [Periods].[Period Start Date]) )
else (0)

EndRange data item:

Code Select Expand

if ( _days_between([FactData].[EndDate], [Periods].[Period Start Date]) >= 0
and _days_between([FactData].[EndDate], [Periods].[Period Start Date]) < [NumberOfDays] )
then
( _days_between([FactData].[EndDate], [Periods].[Period Start Date]) )
else (0)

WithinRange data item:

Code Select Expand

if ( [BeginRange] = 0 and [EndRange] = 0 )
then ( [NumberOfDays] )
else (0)

Finally, create a data item with an expression to add up the three figures [BeginRange] + [EndRange] + [WithinRange]. Use this data item and the period from your date dimension query in the layout.

Easy as pie  :o

[jive]

Or you can have a time table having each days for 40,50,100 years,get an unique index on the date.

1 colon (workdays, Monday to Friday) from 1 the lowset date to N number last day of your period,
first  find the starting date number, second find the ending date number,  substract both of them +1 you have the numbers weekdays between two dates.

If you want to have more information add a column mark the holiday , retrieve the days as explain before but remove the one mark as holiday. this way you will have the business dayThis is what we do here and it's more easy to do that then working with those date function in cognos.
you can have all the columns you need in that table, 6 month period, 3 month period, year etc.etc.

Regards Jacques