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.
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.