KRML_CALENDAR_DAYS and KRML_CALENDAR_MONTHS

These tables, included in the RML, contain sequential dates. KRML_CALENDAR_DAYS contains a record for every day from January 1, 1998, to mid-2011. KRML_CALENDAR_MONTHS contains a record for every month from January 1998 to mid-2011.

These tables can be used to provide a date for organizing and grouping the results of queries.

Sample

A report needs to contain summary information for the number of errors for step 2 in the FIN dev-test-prod workflow, broken down by month. The calendar table KRML_CALENDAR_MONTHS can be used to provide the month-by-month breakdown to join with the ACTIVITY_DATE column in the view MWFL_STEP_ACTIVITIES:

SELECT m.calendar_month MONTH,
       sum(sa.error)    NUM_ERRORS
FROM   krml_calendar_months m,
       mwfl_step_activities sa
WHERE  sa.workflow = 'FIN dev-test-prod'
AND    sa.workflow_step_number = 2
AND    sa.activity_date >= m.start_date
AND    sa.activity_date < m.end_date
GROUP BY m.calendar_month
ORDER BY 1;

The comparison of ACTIVITY_DATE to the START_DATE and END_DATE of the calendar month. This can be very useful for grouping discrete activity dates into aggregate time buckets.