How to extract part of a date range created using a field filter?


I’m trying to do a SQL query similar to:

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'm trying to do same thing, are you able to get a workaround?

@phaetherap You will need to create a View on your database, so Metabase can reference that field, since Field Filters does not support aliasing: - upvote by clicking :+1: on the first post