I’ve run into the situation a few times now where I have a question written in custom SQL (that’s too complex for the native query builder) that I need to allow the person accessing it to set a date, and if the date isn’t set then default to today.
I know that I could work around this by making the field required and not filling in a default, so that the person has to set something every time and I also know that I can set a default as an absolute date. But 95% of the time they’re accessing this specific question, it should be with today’s date plugged in.
Does anyone have any suggestions on how I can work out the SQL so that when the template doesn’t appear it fills now() or other methods on how to handle this?
Here’s an idea. This example will only return one row, but you could extend to more pretty easily (like just treating this as a subquery and joining it to something else).
It uses the SQL parameters feature. The double brackets means the clause is optional, so if the user doesn’t input a specific date, the bracketed code won’t be evaluated. If it’s not evaluated, is defaults to today’s date by ordering by date.
select created_date from my_table [[where created_date = {{x}}]] ORDER BY created_date DESC LIMIT 1
Ah, interesting. Seems like we could enhance the default value selection by allowing relative dates instead of just specific ones. I’ve opened up a new issue to track this idea.
That’s a good suggestion! It makes my SQL more convoluted than I’d prefer, but at least until the enhancement of supporting today that @maz suggested on github happens, it would solve my problem in a reasonable way.