I have an SQL problem that I can't figure out or seem to find any solutions that help out. I am using an SQL serverless pool on Azure Synapse.

My problem is that I can't figure out how to backfill rates for each date until there is an update. For example CNY to USD updated on the 1st and 30th of the month. I need to fill in the same exchange rate as the 1st for every day up until the 30th when the rate changes and so on...

My tables: 1 - Exchange Rate

2 - Calendar Table

This is a problem because I am joining a list of IDs with foreign currencies to the Exchange Rate table based on the creation date. So I can create a project on the 15th of the month, in between the 2 updates and use the latest updated rate for it (ie. the 1sts rate).

I have tried using left joins as a starting point like below:

SELECT distinct t1.[FromCurrencyCode],
t1.[ToCurrencyCode],
t1.[ExchangeRate],
t2.[Calendar_Date]
FROM [dbo].[ExchangeRate] t1 join [dbo].[Calendar] t2 on convert(date,t1.ExchangeDate) = 
t2.Calendar_Date
order by t1.FromCurrencyCode, t2.Calendar_Date

Which results in: [1]: https://i.stack.imgur.com/cNSqz.png

I have looked at a lot of older solutions involving recursive common table expressions (CTEs) but none have seemed to work for me so far. The currencies are not the same across the examples as it is sample data in the tables so you can understand the layout!

Any help would be much appreciated!

CodePudding user response：

If i understand your question, you want to have for every date between two ExchangeDate the same ExchangeRate every day, to do so, you can use this query:

SELECT DISTINCT t1.[FromCurrencyCode]
   ,t1.[ToCurrencyCode]
   ,t1.[ExchangeRate]
   ,t2.[Calendar_Date]
FROM (
    SELECT DISTINCT [FromCurrencyCode]
        ,[ToCurrencyCode]
        ,[ExchangeRate]
        ,ExchangeDate StartExchangeDate
        ,LEAD(ExchangeDate) OVER (
            PARTITION BY [FromCurrencyCode]
            ,[ToCurrencyCode] ORDER BY ExchangeDate
        ) EndExchangeDate
    FROM [dbo].[ExchangeRate]
) t1
JOIN [dbo].[Calendar] t2 ON t2.Calendar_Date >=  t1.StartExchangeDate AND 
    t2.Calendar_Date < t1.EndExchangeDate
ORDER BY t1.FromCurrencyCode
    ,t2.Calendar_Date