I have a table of exchange rates that contain a day and a value column, where the first one is the day of the exchange rate and the other has the value of that day. Then I have another table which has amount value of a KPI in euros, so I need to join these to columns to extract the amount value of the KPI in the currency of the exchange rate table, which is in reales (BRL).
The problem is that the exchange rate table has not all the values from each day. In other words, some days does not appear, which are Saturdays and Sundays. In order to solve that, what I need to do is find the average of the days occured (in the month) if that specific date is not appearing in the table. So for example, 11th of Feb 2023 (Saturday) I don´t have the value, so it calculates the average of the previous 10 days of the month. In the 18th Feb 2023 (saturday) it should have a different value from 11th Feb, because the average of the 17 previous days of the month is different from the average in the 10 previous day.
I reach this query
SELECT
extract(day from ps.summary_date) as day_month,
sum(Cast(GGR_Amt_EUR as decimal (19,8))) GGR_Amt_EUR,
sum(Cast(GGR_Amt_EUR*New_TC_BRL as decimal (19,8))) GGR_Amt_BRL,
CASE WHEN
TipoBRL.Conversion_Rate Is Not NULL and Oreplace(Dim_Fx_Snapshot.to_curr_cd,' ','') ='BRL'
THEN TipoBRL.Conversion_Rate
ELSE
(Select
Cast(AVG(CAST(conversion_rate as decimal(19,8))) as decimal (19,8))Conversion_Rate
From Dim_Fx_Snapshot
Where Oreplace(from_curr_cd,' ','')='EUR' And Oreplace(to_curr_cd,' ','') ='BRL' And Month(snapshot_date)=Month(Date-1) And Year (snapshot_date)=Year(Date-1)
And platform_cd=2
)
END
AS New_TC_BRL
FROM fd_player_summary ps
/**Tipo de cambio BRL*/
Left Join
(
select
distinct
snapshot_date,
CAST(conversion_rate as decimal(19,8)) Conversion_Rate
from Dim_Fx_Snapshot
where Oreplace(from_curr_cd,' ','')='EUR' and Oreplace(to_curr_cd,' ','') ='BRL' and Month(snapshot_date)=Month(Date-1) And Year (snapshot_date)=Year(Date-1)
and platform_cd=2
) TipoBRL on TipoBRL.snapshot_date=ps.summary_date
/**/
WHERE month(ps.summary_date) = month(date-1) and year(ps.summary_date) = year(date-1)
GROUP BY
New_TC_BRL,
day_month
The problem is that in every day that do not exist in the table exchange rate (those Saturdays and Sundays) is giving me the same average value, as I show in this table. So for example the 11th and 18th Feb have the same value. I know is a problem of the case when but I do not know how to fix it. I guess I need a moving average for those days, but no idea.
This might return the expected result without join:
coalesce(Conversion_Rate -- use rate if exists
,avg(Conversion_Rate) -- otherwise moving average of the
over (partition by -- current month
extract(year from snapshot_date)
,extract(month from snapshot_date)
order by snapshot_date
rows unbounded preceding
)
)
This might return NULL if the first day(s) of a month are NULL.
Edit:
Calculate the exchange rate first using a calendar table (of course, use your own calendar table instead of sys_calendar) like this:
select
c.calendar_date
,coalesce(Conversion_Rate -- use rate if exists
,avg(Conversion_Rate) -- otherwise moving average of the
over (partition by -- current month
extract(year from c.calendar_date)
,extract(month from c.calendar_date)
order by c.calendar_date
rows unbounded preceding
)
) New_TC_BRL
from sys_calendar.calendar as c
left join
(
select
distinct
snapshot_date,
CAST(conversion_rate as decimal(19,8)) Conversion_Rate
from Dim_Fx_Snapshot
where Oreplace(from_curr_cd,' ','')='EUR' and Oreplace(to_curr_cd,' ','') ='BRL' and Month(snapshot_date)=Month(Date-1) And Year (snapshot_date)=Year(Date-1)
and platform_cd=2
) AS dt
on c.calendar_date = dt.snapshot_date
Btw, as historical exchange rates don't change, they should be calculated once daily and stored in a table. Your current approach doesn't seem to be efficient.