For our date comparisons we are going to need some date tables. There is lots to Google about Date stuff in Pentaho, but the short version as I got it: you need a date table in your data warehouse for each date field you are going to use. In our example we are going to use the case start date, case end date, main activity start date and main activity end date. So we are going to have 4 date tables with possible dates: dIm_date1, dim_date2, dim_date3 and dim_date4.
This is the create file I got from Owen:
And here is an SQL procedure I grabbed of the web: