Creating Dynamic Lists of Dates or Numbers in a Query

Introduction

Throughout my many years of work in IT, there have been many times when I've needed to write some SQL that required a list of numbers or dates. Originally, the only way I could achieve this was to create a table in the database. The downside of this was that it had to be maintained manually and if forgotten, could lead to functionality loss. Also not being a DBA, it was often difficult to get approval for such a table.


Create a List of Numbers

In Oracle SQL, this functionality is easily achieved by using the CONNECT BY clause

The following example will provide you with a list of numbers from 1 to 12. This is perfect for months of a year.

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 12
                      

Select all Open in new window

Another example I use this for is to create a list of percentages between 0 and 100

SELECT (LEVEL-1) COL FROM DUAL CONNECT BY LEVEL <= 101
                      

Select all Open in new window

So, using variations of these queries, you can create any number of variations to the lists of numbers. Below are a few more examples.

-- 0 to 100 with an intival of 5 
                      SELECT (LEVEL-1) * 5 COL FROM DUAL CONNECT BY LEVEL <= 21
                      
                      -- List if the last 20 years
                      SELECT extract(year from sysdate) - LEVEL + 1 COL FROM DUAL CONNECT BY LEVEL <= 21
                      

Select all Open in new window

Create a List of Dates

Using similar logic from above getting a list of numbers, you can apply it to dates to achieve a list of required dates. The most common reason for me to use a list of dates is to join them to a GROUP BY query that I want to use to populate a chart. The GROUP BY query may not contain all the dates in the month so if I join the list of dates to my GROUP BY query using a LEFT OUTER JOIN, my chart will always have every date in the month.

Below is a query containing all the dates from last month.

SELECT trunc(add_months(sysdate, -1), 'mon') + LEVEL - 1 ADATE
                      from dual
                      connect by level <= to_number(to_char(last_day(add_months(sysdate, -1)), 'DD'))
                      

Select all Open in new window

These tips have helped me over the past few years and I hope that someone else can get use out of them. I have found that once you master one, variations are very easily created.