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.