Hi @Johan Van Noten​ 

I got a work around it. If this helps you. Below is the query I wrote

Target Query:

create or replace function TestAverage(DateBudget Date) RETURNS FLOAT

  return select Avg(pd.Amount) as Amount

        from TestTable1 as pd 

          left join TestTable2 er

            on er.PK = pd.Exchange_Rate_PK

          where sign(DATEDIFF(DateBudget, date_add(to_date(pd.From_Date), -1))) = 1

             and sign(DATEDIFF(DateBudget, date_add(to_date(pd.To_Date), 1))) = -1;

Original Query:

select AVG(pd.Amount) as Amount

      from TestTable1 as pd 

        left join TestTable2 er

          on er.PK = pd.Exchange_Rate_PK

      where fci.DATEBUDGET >= pd.From_Date

        and fci.DATEBUDGET <= pd.To_Date