Given a date I want to find the last date that is not a weekend and does not exist in a list of other dates

Question

I have a table MY_TABLE that has a column ALL_DATES whose values are just a bunch of dates

I am trying to write a UDF where given a date, I want to return the previous date that is not a weekend and does not exist in ALL_DATES from my table. If either of these conditions is not met I would like to continue getting the date before that until both conditions are met (not a weekend and not in the list of dates)

So far for my function I have

CREATE OR REPLACE FUNCTION GET_PREVIOUS_DATE(_CURRENT_DATE VARCHAR)
RETURNS DATE
$$
SELECT DATEADD(day, -1, _CURRENT_DATE) AS PREVIOUS_DATE
$$;

I know I can check if it exists in the list of dates by doing

CREATE OR REPLACE FUNCTION GET_PREVIOUS_DATE(_CURRENT_DATE VARCHAR)
  RETURNS BOOLEAN
$$
SELECT EXISTS (
  SELECT *
  FROM MY_TABLE
  WHERE ALL_DATES = _CURRENT_DATE
)
$$;

But I'm not sure how to combine these two sqls along with checking the day of the week of PREVIOUS_DATE to return the last date that satisfies my conditions

Answer 1

We can use snowflake Snowscript to achieve this. Only difference is it will be using a stored procedure.

IF requirement is compulsory for function you can go with Python or Javascript UDF.

create or replace procedure GetPrevDate(cd date)
returns variant
language sql
execute as caller
as 
BEGIN

    LOOP
        if (not exists(select 1 from alldates where dt = :cd) 
            and dayofweek(:cd) <> 0 
            and dayofweek(:cd) <> 6)
        then 
            return :cd;
        else
            cd := dateadd('day',-1,:cd);
        end if;
    end LOOP;
    
    END;
        
call GetPrevDate('2023-07-21');