Calculating number of days between two dates

Question

I have a dimension that looks like this:

ID
Date Work Stopped
Date Work Resumed

I also have a Date dimension which has a hierarchy:

Date -> Fiscal Week -> Fiscal Month -> Fiscal Quarter -> Fiscal Year

I would like to generate a report breakdown of [Date Work Resumed] -  [Date Work Stopped] (number of lost working days) but on a month-to-month basis:

November: 20 days
December: 5 days
January: 31 days
etc...

So if the difference between the resumed and stopped dates goes over a month boundary, it should only sum up until the end of the month. I need the same behaviour for the entire hierarchy (from fiscal week to fiscal year).

Tricky one!! :(

Cheers

Tyson

Answer 1

I would do this as part of the ETL stage. Calculating the datediff in SQL when you load the table (or using a calculated column). The best way to deal with the end of week, end of month splits would be to generate extra rows during the ETL.

---

http://darren.gosbell.com - please mark correct answers

Answer 2

I ended up writing a stored procedure to calculate & store the number of days at the fiscal month level. The data is then surfaced in the cube directly. Thanks for your suggestion.