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
Hi galpic2
This looks like SQL to me:
Year(getdate()-1) + IIF(month(getdate()-1) >= 4, 1,0) -2
Return the Year of yesterday then if the month is april onwards add 1 year otherwise add nothing.
Reduce this calculated FY by 2 years.
Unfortunately our fiscal year doesn't align with calendar dates, so one year the end of the fiscal year could be on April 30, the next year it could be on April 24, so just taking today's date and subtracting x number of days/months/years won't always give me the correct answer and would lead to pulling or excluding data that I don't want to pull or exclude.