WITH dates AS (
SELECT GENERATE_SERIES({{data_cadastro_start}}::date, {{data_cadastro_end}}::date, '1 day') AS current_date
), processos AS (
SELECT mv.status_processo_id, DATE_TRUNC('day', processo.data_cadas) AS data_cadastro FROM adm_siga.processo
INNER JOIN adm_siga.movimento_processo AS mv ON mv.processo_id = processo.processo_id
WHERE {{data_cadas}}
)
SELECT dates.current_date, 'Protocolada' AS status, COUNT(processos.status_processo_id) FROM dates
LEFT OUTER JOIN processos ON dates.current_date = processos.data_cadastro AND processos.status_processo_id = '0001'
Here {{data_cadas}} is a date range field filter. I’d like to get its start and end dates to use in the GENERATE_SERIES(). I’m using GENERATE_SERIES() to count values for all dates in the range, including dates that have no data. Is there any way to do that? Like {{data_cadas.start_range}} and {{data_cadas.end_range}} or something similar? Otherwise the only way I see to solve this is to use 2 separate variables instead of a field filter.
I have an example to get difference between dates in days using datarange variable.
1st. How to split dates? - Simple, subquering any table (logs, access, etc.) with daterange variable in WHERE clause.
SELECT count(*) AS count,
(SELECT MIN("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}}) AS "start_date",
(SELECT MAX("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}}) AS "end_date",
...
FROM
...
2nd, Using DATE_PART function to operate with the subqueries results
SELECT
...
DATE_PART('day', (SELECT MAX("public"."database"."crm_Date") FROM "public"."database" WHERE {{date_range}})::timestamp - (SELECT MIN("public"."database"."crm_date") FROM "public"."database" WHERE {{date_range}})::timestamp)) AS "Days in range",
....
FROM ...
WHERE ..
GROUP BY ...
With this subquery tip you can access to individual "date.start" and "date.end" data from data range variable in Metabase SQL.
I don't understand how aliasing relates to the original question, which is: how to access the "start" and "end" value of a date range field filter in a SQL question.
I'm doing the exact same thing as @vitorbaptista and, like him, I'm forced to use 2 separate variables instead of a field filter. That wouldn't be too much of a problem, but all my dashboards already use a date range field filter. So now I have a date range + a start date and an end date, all set to the same date range!
Is there still no solution to this problem? (PS: I don't consider DabidGrau idea to be a solution)
on the first post