Forum Discussion
How to filter initial data pull using today's date as a reference for another column
sql doesn't use iif statements for the record
your where clause could look something like this.
assuming you can do a cte (common table expression) in snowflake
with cte_fiscal as (
select
max(FIS_YR_ID) as max_year
from
tablename
where
day = dateadd('day', -1, currentdate())
)
select
*
from
tablename
cross join cte_fiscal cte
where
FIS_YR_ID >= max_year -2
and FIS_YR_ID <= max_year
where are you trying to do this exactly? how are you ingesting this data, what is your source? It is always preferable t provide sample data with your expected output.
I'm pulling from a snowflake table and the data is more or less laid out like below, pretty basic, DAYS in one column, corresponding FIS_YR_ID in another.
The original source data has data going back decades and looking forward decades but I only need to pull in the last 2 years of fiscal dates. I'm able to easily say I only want Days less than today so I don't pull in all of the future data, but I'm trying to figure out how to tell SQL to filter FIS_YR_ID by looking up today's FIS_YR_ID (2025) and just subtracting 2 (2023) within the initial query.
| DAY | FIS_YR_ID |
| 8/5/2024 | 2025 |
| 8/4/2024 | 2025 |
| .... | .... |
| 8/4/2023 | 2024 |
| ... | ... |
| 8/4/2022 | 2023 |
| ... | ... |