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.
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
dates table contains:
datetime– A date representing a certain period
day– The day in
month– The month in
year– The year in
balance table, there are intervals in which the balance amount has a range of dates (defined by
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_to from the
balance table) and define what goes in between them. In this case, it will be the
datetime attributes from the
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.