No calendar table? No problem!

In my early days as an analyst, I found myself needing to report on various performance measures with seemingly problematic definitions. These measures required computing the time elapsed between two events. One memorable scenario involved monitoring a group’s ability to resolve JIRA tickets within a defined SLA or number of days. Simple enough – right? For each ticket, simply compute the difference between the ticket’s opening date and time and the corresponding closing date and time values. In this case, wrong. The measure’s definition also included the requirement to exclude weekends. This is a fair and logical ask given the associates responsible for resolving the tickets did not work on weekends. The troublesome part was a calendar table containing the necessary data to programmatically differentiate weekdays from weekends did not exist within the database schemas available to me. I had two options, ask the team managing the database containing the JIRA ticket information to create the calendar table or develop another means independently. Due to the urgency of the request and the slim possibility of the database team quickly creating a calendar table, I decided to do the latter.

At the time, my intuition was not all there for this type of problem; however, with some thought, I landed on combining the concepts of recursion and common table expressions (CTE) to solve it. This turned out to be a good option given I did not have CREATE privileges, but it also meant the object containing the calendar data would no longer be a table. The calendar data would need to be generated at query runtime and the temporary named result set (i.e., the output of the CTE) referenced within the query.

Back to the problem. The reporting on JIRA tickets covered those tickets created within the current year and previous year, so I knew I needed the CTE to generate, at a minimum, one row per date value within that timeframe. As far as attributes were concerned, I only needed the date value and an attribute specifying whether the date value corresponded to a weekday or weekend. The code I used to generate the calendar data can be found below. For clarity, see the corresponding output. Keep in mind, this output was generated on February 11th, 2024, 3PM CST.

WITH calendar (date_value, day_name) AS (
    SELECT
        ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AS date_value,
    	TRIM(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12), 'DAY')) AS day_name
    FROM
        dual
    UNION ALL
    SELECT
        date_value + 1,
    	TRIM(TO_CHAR(date_value + 1, 'DAY'))
    FROM
        calendar
    WHERE
        calendar.date_value < TRUNC(SYSDATE)
)
SELECT
	date_value,
    day_name
FROM
    calendar;

To continue this story, I have created a table called ticket_detail containing fictitious information related to JIRA tickets. The fictitious data can be found below. If how I generated this data is of interest to you, then you can click on the icon next to Ticket Generation Code. To hide the data, click the icon again.

EXEC DBMS_RANDOM.SEED(42);

CREATE TABLE ticket_detail AS
	WITH generate_ticket (ticket_id, open_date) AS (
		SELECT
			1 AS ticket_id,
			CAST(
				ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)
				+ INTERVAL '1' HOUR
				* DBMS_RANDOM.VALUE(1, (SYSDATE - ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)) * 24)
				AS TIMESTAMP
			) AS open_date
		FROM
			dual
		UNION ALL
		SELECT
			generate_ticket.ticket_id + 1,
			CAST(
				ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)
				+ INTERVAL '1' HOUR
				* DBMS_RANDOM.VALUE(1, (SYSDATE - ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12)) * 24)
				AS TIMESTAMP
			) AS open_date
		FROM
			generate_ticket
		WHERE
			generate_ticket.ticket_id < 500
	),
	close_ticket (ticket_id, open_date, close_date) AS (
		SELECT
			generate_ticket.ticket_id,
			generate_ticket.open_date,
			CAST(
				generate_ticket.open_date
				+ INTERVAL '1' HOUR
				* DBMS_RANDOM.VALUE(1, (EXTRACT(DAY FROM SYSDATE - generate_ticket.open_date) * 24)
					+ EXTRACT(HOUR FROM SYSDATE - generate_ticket.open_date)
				)
				AS TIMESTAMP
			) AS close_date
		FROM
			generate_ticket
	),
	weekend_adjustment (ticket_id, open_date, close_date) AS (
		SELECT
			close_ticket.ticket_id,
			CAST(
				DECODE(TO_CHAR(close_ticket.open_date, 'D'),
					1, close_ticket.open_date + 1,
					7, close_ticket.open_date + 2,
					close_ticket.open_date
				)
				AS TIMESTAMP
			) AS open_date,
			CAST(
				DECODE(TO_CHAR(close_ticket.close_date, 'D'),
					1, close_ticket.close_date + 1,
					7, close_ticket.close_date + 2,
					close_ticket.close_date
				)
				AS TIMESTAMP
			) AS close_date
		FROM
			close_ticket
	),
	ticket_detail (ticket_id, open_date, close_date) AS (
		SELECT
			ROW_NUMBER() OVER (
				ORDER BY weekend_adjustment.open_date
			) AS ticket_id,
			CASE
				WHEN weekend_adjustment.close_date > SYSDATE
					THEN weekend_adjustment.open_date - INTERVAL '1' DAY
				ELSE
					weekend_adjustment.open_date
			END AS open_date,
			CASE
				WHEN weekend_adjustment.close_date > SYSDATE
					THEN weekend_adjustment.close_date - INTERVAL '1' DAY
				ELSE
					weekend_adjustment.close_date
			END AS close_date
		FROM
			weekend_adjustment
	)
	SELECT
		ticket_detail.ticket_id,
		ticket_detail.open_date,
		ticket_detail.close_date
	FROM
		ticket_detail
	ORDER BY
		ticket_detail.open_date;

With the calendar data now at my disposal, I knew I could easily compute the number of Saturdays and Sundays occurring between each ticket’s open date value and close date value. The code below captures my strategy for doing so. For clarity, review the code’s corresponding output.

WITH calendar (date_value, day_name) AS (
    SELECT
        ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12) AS date_value,
    	TRIM(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -12), 'DAY')) AS day_name
    FROM
        dual
    UNION ALL
    SELECT
        date_value + 1,
    	TRIM(TO_CHAR(date_value + 1, 'DAY'))
    FROM
        calendar
    WHERE
        calendar.date_value < TRUNC(SYSDATE)
),
ticket_duration (ticket_id, open_date, close_date, interval_value) AS (
	SELECT
		ticket_detail.ticket_id,
		ticket_detail.open_date,
		ticket_detail.close_date,
		(
			(ticket_detail.close_date
			- ticket_detail.open_date)
			- INTERVAL '1' DAY
			* (
			SELECT
				COUNT(*)
			FROM
				calendar
			WHERE
				day_name IN ('SATURDAY', 'SUNDAY')
				AND date_value > ticket_detail.open_date
				AND date_value < ticket_detail.close_date
			)
		) AS interval_duration
	FROM
		ticket_detail
)
SELECT
	ticket_duration.ticket_id,
	ticket_duration.open_date,
	ticket_duration.close_date,
	ticket_duration.interval_value,
	CAST(
		EXTRACT(DAY FROM ticket_duration.interval_value)
		+ EXTRACT(HOUR FROM ticket_duration.interval_value) / 24
		+ EXTRACT(MINUTE FROM ticket_duration.interval_value) / 1440
		+ EXTRACT(SECOND FROM ticket_duration.interval_value) / 86400
        AS NUMBER
	) AS days_elapsed
FROM
	ticket_duration;

Long-term Solution
My story above explains how I was able to make the best out of my less-than-ideal situation by implementing a short-term solution in the form of a recursive common table expression to generate my calendar data. This solution has a few drawbacks limiting its usability, including the following:

– The attributes describing each date value are limited to the day name.
– The date range captured in the calendar data is unlikely to be sufficient for all reporting scenarios.
– The calendar data is only available to a single user, within a single session, and within the scope of a single statement.

To address the first two drawbacks, the contents of the calendar table can be expanded in terms of attributes and the date range captured. The code below generates one row per day, beginning with the first of the year two years prior and ending with the current date (i.e., February 12th in UTC). For each date value, additional attributes have been included to support a wide array of reporting requirements. Please be aware that I have made use of Oracle specific functions, so if you are planning on running the code in other database products, then you will likely need to swap out some of the functions.

WITH calendar (
    date_value, year_value, month_value, year_number,
    quarter_number, month_number, week_number, day_of_year_number,
    day_of_month_number, day_of_week_number, month_name,
    day_name, weekend
) AS (
    SELECT
        ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24) AS date_value,
		ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24) AS year_value,
		ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24) AS month_value,
		EXTRACT(YEAR FROM ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24)) AS year_number,
		TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'Q') AS quarter_number,
		EXTRACT(MONTH FROM ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24)) AS month_number,
		TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'W') AS week_number,
		TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'DDD')) AS day_of_year_number,
		TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'DD')) AS day_of_month_number,
		TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'D') AS day_of_week_number,
    	TRIM(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'MONTH')) AS month_name,
    	TRIM(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'DAY')) AS day_name,
		DECODE(
			TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'YEAR'), -24), 'D'),
			1, 1,
			7, 1,
			0
		)
    FROM
        dual
    UNION ALL
    SELECT
        calendar.date_value + 1,
    	TRUNC(calendar.date_value + 1, 'YEAR'),
		TRUNC(calendar.date_value + 1, 'MONTH'),
		EXTRACT(YEAR FROM calendar.date_value + 1),
		TO_CHAR(calendar.date_value + 1, 'Q'),
		EXTRACT(MONTH FROM calendar.date_value + 1),
		TO_CHAR(calendar.date_value + 1, 'W'),
		TO_NUMBER(TO_CHAR(calendar.date_value + 1, 'DDD')),
		TO_NUMBER(TO_CHAR(calendar.date_value + 1, 'DD')),
		TO_CHAR(calendar.date_value + 1, 'D'),
		TRIM(TO_CHAR(calendar.date_value + 1, 'MONTH')),
		TRIM(TO_CHAR(calendar.date_value + 1, 'DAY')),
		DECODE(
			TO_CHAR(calendar.date_value + 1, 'D'),
			1, 1,
			7, 1,
			0
		)		
    FROM
        calendar
    WHERE
        calendar.date_value < TRUNC(SYSDATE)
)
SELECT
	calendar.date_value,
	calendar.year_value,
	calendar.month_value,
	calendar.year_number,
    calendar.quarter_number,
	calendar.month_number,
	calendar.week_number,
	calendar.day_of_year_number,
    calendar.day_of_month_number,
	calendar.day_of_week_number,
	calendar.month_name,
    calendar.day_name,
	calendar.weekend
FROM
	calendar;