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):
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({{Date-Parameter}}:end) IS NULL
THEN DATE >= date({{Date-Parameter}}:start)
ELSE DATE BETWEEN date({{Date-Parameter}}:start) AND date({{Date-Parameter}}:end)
END
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.
These notes should be in our documentation soon but in the meantime:
Custom SQL quirk based on data warehouse dialect (given we have a control with id “Date-Range-Control”)
i. Snowflake: the parameter is a VARIANT
Ex: to_timestamp({{Date-Range-Control}}[‘start’]) or to_timestamp({{Date-Range-Control}}:start)
ii. BigQuery, Trino, Databricks: the parameter is a STRUCT
Ex: {{Date-Range-Control}}.start
iii. Redshift: the parameter is a SUPER
Ex: select date_range.start from (select {{Date-Range-Control}} date_range)::timestamp
iv. Postgres: the parameter is a JSONB
Ex: ({{Date-Range-Control}}->>‘start’)::timestamptz
If you want to manually extract the start and end values of your date range parameter, you can convert the parameter to JSON and do an extract. Below example uses Snowflake functions:
This is awesome! Could you use the same start/end references for embedding?