Get a list of Quarters by Year with Recursive CTEs (WITH-clause)

The introduction of Recursive CTEs (recursive WITH-clauses) turned the SQL:1999 standard into a turning complete programming language. That means (simplified) that a language can solve every computational problem, given infinite resources.

As you can see in the name of the standard and the picture, it’s been around for quite a while and is implemented in most relational databases.

It is a compelling addition and can – besides other things – be used to create rows “from thin air”.

For example, we can generate a list of quarters by year for whatever range we want:

with year_quarter(quarter, year) as ( -- Recursive CTEs always start with a base row select 1 quarter, 2023 year from dual union all -- and are then followed by sql that describes -- how each following row is created select -- For quarter we count backwards until 1 is reached case when prev.quarter <= 1 then 4 else prev.quarter-1 end as quarter, -- For year we only count backwards if quarter is 1 case when prev.quarter <= 1 then prev.year-1 else prev.year end as year from year_quarter prev -- it's very important to have a limit for the recursion where prev.year >= 2019 and prev.quarter between 1 and 4 ) select * from year_quarter -- We will get one 2018 row by the recursive CTE -- so let's remove it where year >= 2019; 

This will give us a nice list of quarters and years:

You might wonder why we have the and prev.quarter between 1 and 4 in the CTE, because we wouldn’t really need it.

If you remove it, though, you will get an ORA-32044 error that cycles were detected in your recursion. This is because by default Oracle uses the column aliases that are used in the where clause of the second select to determine which columns will form a cycle.

In this case, a row forms a cycle if one of its ancestor rows has the same values for all the columns in the column alias list for query_name that are referenced in the WHERE clause of the recursive member.

You can circumvent this by adding a cycle clause in which you define the columns that are used for cycle detection:

with year_quarter(quarter, year) as ( ... from year_quarter prev where prev.year >= 2019 ) cycle quarter, year set is_loop to 'Y' default 'N' select quarter, year from year_quarter where year >= 2019; 

Getting the start and end date of each quarter

With this baseline data, we can now also select the start and end dates of each quarter, using the last_day function that is built-in in Oracle:

with year_quarter(quarter, year) as ( select 1 quarter, 2023 year from dual union all select case when prev.quarter <= 1 then 4 else prev.quarter-1 end as quarter, case when prev.quarter <= 1 then prev.year-1 else prev.year end as year from year_quarter prev where prev.year >= 2019 and prev.quarter between 1 and 4 ), dates as ( select year, quarter, to_date(year||'-'||(quarter*3-2), 'YYYY-MM') start_date, -- we're using the very handy last_day function here last_day(to_date(year||'-'||(quarter*3), 'YYYY-MM')) end_date from year_quarter ) select * from dates where year >= 2019; 

This gives us some very nice base data to be combined with other queries (e.g. getting the number of arrivals and departures of spaceships for each quarter, even if there are quarters with no arrivals at all)

Have fun and never forget: SQL is your friend!

The post Get a list of Quarters by Year with Recursive CTEs (WITH-clause) appeared first on Developer Sam.