How to use a date range parameter in custom SQL

SQL Runner
#1

Let’s say you’d like to have a custom SQL table in your Workbook filter out dates in a DATE column according to any of the many possible selections in the date range parameter (pictured below):

image
image1146×882 57.1 KB

This can be accomplished using some iteration of the following CASE/WHEN statement in custom SQL:

SELECT * FROM table
WHERE
CASE WHEN date({{Date-Parameter}}:start) IS NULL
THEN DATE <= date({{Date-Parameter}}:end)
WHEN date({{New-Control}}:end) IS NULL
THEN DATE >= date({{Date-Parameter}}:start)
ELSE DATE BETWEEN date({{Date-Parameter}}:start) AND date({{Date-Parameter}}:end)
END

#2

:exploding_head: This is awesome! Could you use the same start/end references for embedding?

#3

I haven’t ever done that before, but it would be nice if it’s possible. I’ll give it a go and get back to you!

#4

I’m hoping I understood what you meant by “the same start/end references”… but anyway, I made a user-backed embed with a date range parameter control and a custom SQL table (as described in this post)… and it seemed to work right out of the box with the published control selection. Please let me know if this answers your question.