Useful SQL Patterns: Date Generator

As you start coding in SQL, you will use some statements and techniques over and over again. We call these “SQL patterns”. This series will look at the most common SQL patterns and consider how to use them.

SQL patterns, such as the pivot pattern we discussed last week, can save you a lot of time and effort. Suppose you are asked to get a range of days in a financial quarter, but the only records you have are for the start and end dates of each quarter. Do you have to manually add all the missing days? No. You can use a technique known as a date generator to fill in the gaps. This SQL pattern is commonly used with date and time data, and it can be done granularly, down to every second of a year. Another name for this technique is “expanding to value”, but we’ll stick with “generator”.

Expanding Date Values with a Generator

So you want to have your data expanded to include every data point in a range. For the purposes of our example, we’ll say that you need to get the first day of every month in a year. Let’s use the following tables to see how this could be done.

The balance table contains:

• id – The table’s primary key
• account – The account ID number
• customer – A unique number assigned to each customer
• amount – The customer’s account balance during a specific time
• datetime_from – The date the balance was entered
• datetime_to – The date the balance changed

The dates table contains:

• datetime – A date representing a certain period
• day – The day in datetime
• month – The month in datetime
• year – The year in datetime

In the balance table, there are intervals in which the balance amount has a range of dates (defined by datetime_from and datetime_to) for a particular client and account.

This data looks like:

We need the dates table to perform the expansion; it contains all the dates needed for our case.

Let’s say it holds the first day of every month of 2017:

To expand the row set, we use the BETWEEN operator. We specify the date range (using datetime_from and datetime_to from the balance table) and define what goes in between them. In this case, it will be the datetime attributes from the dates table.

The query looks like this :

	SELECT
  dates.datetime,
  customer,
  sum(amount) AS amount
FROM
  balance
  JOIN dates ON (dates.datetime BETWEEN balance.datetime_from AND balance.date_to-1)
GROUP BY customer, dates.datetime
ORDER BY datetime;

And the resulting data set is :

Learning More About Generators

Now we’ve seen a generator, a query which returns a bigger dataset than what it started with. Be careful writing these types of queries: a small mistake can really slow down the execution time.

You can read more about adding information to tables in this data warehousing article. Or if you want to find out more about SQL Patterns, check out the beginning of the series here.