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
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.
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
Good to know, thank you vanessafvg! I couldn't tell which brand of SQL they were writing, since this is a Microsoft Forum I assumed T-SQL which does support IIF.
i have never written an iif statement in sql, and ive done sql development for over 25 years 🙂 sql uses a case statement for if.
Ahh so Case is better performance than IIF ? That is good to know thank you! vanessafvg Always happy for advice from the masters.
One that blew my mind was dragging the columns in the results pane, when was that added 🤔😂
interestingly in later version it does seem to have this syntax available, so my bad, however i see it still converts it to a case statement anway
This worked!! Thank you!!
The only thing I had to change was
'day = dateadd('day', -1, currentdate())'
it didn't recognize 'currentdate()' so I just had to change it to 'getdate()' and it worked perfectly!
