Advanced uses of the CASE statement in SQL — Part 2: Conditional WHERE clauses

Another fantastic but not often discussed use of the CASE statement

In my last article, I wrote about using the CASE statement inside the SUM() function. Today I’d like to introduce another very, very handy place to use the CASE statement that I don’t see taught very often — inside the WHERE statement, to allow for conditional filtering.

There are many ways you can use this technique, but I want to talk about the most common use-case for me, which is filtering for different date ranges, based on which Day of the Week the query is being executed.

The situation:

You are an analyst who’s been tasked with providing a daily report based on yesterdays sales data — easy enough right? You can manually input yesterdays date in your WHERE clause, but that requires a code change every day. One answer would be using a relative date instead, like this:

current_date-INTERVAL '1' DAY simply returns yesterday’s date, so you’re just asking the query for yesterday’s data. Note: You may want to add another condition to exclude today’s data if working on a live database.

Now, every time you run the code it’s always looking at yesterday’s order volume. This is a great start, but what happens on Mondays? If you don’t work over the weekend, you’re only going to get Sunday’s data — Friday and Saturday are always going to be missing and this will also require a code change every Monday to resolve.

The solution? WHERE <…> CASE WHEN

By combining the WHERE clause, with a CASE statement that returns a different relative date based on the Day Of Week, this solution provides an elegant way to dynamically change the relative number of days your code looks back over, based on what day the code is run. Check it out:

What’s going on here?

In PostgreSQL, extract('dow' from current_date)returns a single digit ranging from 0–6 based on which day of the week it is. If the query is run on Monday, it will return a 1 to our CASE statement. When that condition is met, the CASE statement returns current_date-INTERVAL '3' DAY to the WHERE clause, which tells the query to return data for the past 3 days. If that condition is not met, because it’s a Tuesday for example, it returns current_date-INTERVAL '1' DAY , yesterday.

Now every time you run the query, it will return data from the correct date range, with no code changes required at all!

Note: different flavours of SQL have slightly different ways to extract the Day of Week, as well as get the current date. There is also some variation in which number a particular day is equal to. Some will return a 0 for Sunday, some will return a 1.

There are lots of use cases for a CASE statement in the WHERE clause, so go forth and conditionally filter stuff!

Until next time!