Query to pick value depending on date-CodePudding

I have a table with exchange rates which update only when a new exchange rate comes, that is, the only the date that the new rate entered is recorded. however the system has logic to say if any date fall within a particular date, it picks the corresponding exchange rate

i would like to have a query which picks the required exchange rate given any date supplied, i.e., pick the rate from the period.

WITH ListDates(AllDates) AS
(    SELECT cast('2015-11-01' as date) AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < getdate())
SELECT  ld.AllDates,cr.effective_from,cr.rate_against_base
FROM ListDates ld
left join CurrencyRatetable cr on cr.effective_from between cr.effective_from and ld.alldates
option (maxrecursion 0)

CodePudding user response：

I guess you might want to achieve the required result using the window function LEAD. Following an example:

DECLARE @t TABLE(effective_from date, rate_against_base decimal(19,4))

INSERT INTO @t VALUES
('2000-01-01', 1.6)
,('2016-10-26', 1)
,('2020-07-13', 65.8765);

DECLARE @searchDate DATE = '2023-01-17';

WITH cte AS(
SELECT effective_from
      ,ISNULL(LEAD(effective_from) OVER (ORDER BY effective_from), CAST('2049-12-31' AS DATE)) AS effective_to
      ,rate_against_base
  FROM @t
)
SELECT rate_against_base
  FROM cte
  WHERE @searchDate >= effective_from
    AND @searchDate < effective_to

CodePudding user response：

You can use a CROSS APPLY or OUTER APPLY together with a TOP 1 subselect.

Something like:

WITH ListDates(AllDates) AS (
    SELECT cast('2015-11-01' as date) AS DATE
    UNION ALL
    SELECT DATEADD(DAY,1,AllDates)
    FROM ListDates 
    WHERE AllDates < getdate()
)
SELECT ld.AllDates, cr.effective_from, cr.rate_against_base
FROM ListDates ld
OUTER APPLY (
    SELECT TOP 1 *
    FROM CurrencyRatetable cr
    WHERE cr.effective_from <= ld.alldates
    ORDER BY cr.effective_from DESC
) cr
ORDER BY ld.AllDates
option (maxrecursion 0)

Both CROSS APPLY or OUTER APPLY are like a join to a subselect. The difference is that CROSS APPLY is like an inner join and OUTER APPLY is like a left join.

Make sure that CurrencyRatetable has an index on effective_from for efficient access.

See this db<>fiddle.