Building Month Calendars Using SQL and PL/SQL

Each date value consists of the month and year value references. Therefore, it is often necessary to create reports referencing the defined granularity. For example, the evaluation of employee attendance, a project summary, or resource consumption is commonly associated with month precision. Moreover, individual activities can be bordered by the left and right borders of the duration frame. Thus, it can be necessary to get the visual form of the month in terms of individual day’s serial numbers, as well as weekday references. In the past, this had to be hardcoded. Nowadays, mostly pre-prepared visual components forming the calendar can be used.

Nevertheless, in our opinion, it is good to summarize the principles of building calendars and focus on weekday references with an emphasis on the calendar being defined by PL/SQL code, followed by the direct SQL statement approach. The weekdays can be placed either as columns or can be...

Defining a calendar using PL/SQL

You might’ve been in a situation where you need to agree on meeting dates, synchronize activities and events, or write down deadlines for individual tasks and activities. When you receive an invitation to a conference, meeting, or just an online call, you usually open Google Calendar or your smartphone calendar app and check your availability. What do you primarily see? A calendar that’s daily or monthly organized. If you click on a specific day, hour references are loaded.

However, many times, you don’t need to know the exact time of the activity. You will get a task from your boss that needs to be completed by the end of the week. But they do not need to know exactly what time you will do it. It’s enough to ensure that the job is done and not exactly when it was finished. Similarly, when you are asked to prepare a vacation schedule, which precision will be used? Yes, daily granularity. In the same way, for an employment...

Modeling a calendar using SQL

Although it is possible to hardcode a calendar using a PL/SQL block, it can look a bit complicated at first sight.

Now, let’s look at the monthly calendar definition using SQL. For simplicity, we will use the month extracted from the sysdate value, but generally, it can be applied to any specified DATE value. Note that the processing can be optimized by grouping multiple select statements together. However, for explanatory purposes, step-by-step processing is more convenient. Therefore, individual steps are nested. Each step takes the data from the preceding processing.

Step 1 – Getting the day numbers for each day in the month

The goal of this step is to get the same number of rows as the number of days in the month. To do that, the dual table is referenced, which holds just one row. Therefore, the CONNECT BY LEVEL clause is used with the value expressing the numerical representation of the difference between the last day of the...

Name day management

The internet is full of people, social networking, individual names, and their characteristics. For the purposes of information systems, applications, and websites, it may be useful to display a list of names celebrating their name day today, or on any user-defined date. By browsing the net, it is easy to find a list of name days for a particular country in the form of three columns – the day of the month, followed by the month reference, and a list of names, which can be normalized or aggregated together to form one composite value. One way or another, it is possible to get a list based on the date, or vice versa, to get the celebration day for an individual name. In this section, the table’s structure is discussed, followed by a definition of the individual methods encapsulated by the package.

A table references the day, the month, and the value itself, as can be seen in the following snippet:

create table nameday_tab
 (day_val integer,
 ...

Summary

This chapter dealt with the monthly calendar composition through our own implementation. The principles were based on building a list of days followed by a weekday association. It was done with PL/SQL, as well as SQL. The focus was on the proper positioning of the days on the calendar. A calendar can be filled with individual events, birthdays, or name days. Therefore, a definition of a package managing name days was introduced and discussed.

So far, we have been dealing with temporality using data model extensions. However, the relational paradigm is associated with transactions ensuring isolation and durability. These key elements are supervised by transaction logging – UNDO and REDO structures. By extracting values from logs, historical data can be reconstructed.

The next chapter deals with the Flashback technology, providing you with images of the database, table, or query result as they existed at the defined time point or reference. Thus, if the transaction...

Questions

1. The monthly calendar is limited by the first day of the month. Which function provides you with a particular DATE value related to sysdate?
   1. ROUND(sysdate, 'MM')
   2. TRUNC(sysdate, 'MM')
   3. FIRST_DAY(sysdate)
   4. EXTRACT(month from sysdate)
2. From the other perspective, the monthly calendar is limited by the last day of the month. Which statement provides you with midnight of the last day of the month?
   1. select LAST_DAY(sysdate) from dual;
   2. select TRUNC(sysdate) + 1 from dual;
   3. select TRUNC(ADD_MONTHS(sysdate, 1), 'MM')-1 from dual;
   4. select ROUND(sysdate) -1 from dual;

Further reading

• Designing a Calendar Table by Ed Pollack: In this text, the calendar is extended by applying holidays, business days, seasons, and so on. Although the solution is not implemented in Oracle Database, it still references SQL, introducing and highlighting principles. However, it is always possible to apply syntax and implement your own solution in any relational database. Text and implementation are available via this link: https://www.sqlshack.com/designing-a-calendar-table/.
• Creating a date dimension or calendar table in SQL Server by Aaron Bertrand: In this text, a calendar and a date dimension table are created using a set-based solution, making it powerful and easily customizable. Text and implemented code are available via this link: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/.