將只有一個日期的資料，依前後日期組成區間資料 - 以匯率表為例子

前言

有一個匯率資料表，只有記錄匯率改變的日期，如果想要很到該匯率的區間，如下，

那 SQL 要如何下呢?

實作

很簡單，可以使用 LEAD 及 CTE 來達成。 利用 LEAD 來將幣別切分，並依開始日期排序，就可以將下個日期取出來。
再將那個日期減去一日就可以了。
測試SQL如下，

;With CurrRate 
as
(
    Select 'NT' as RefCnt, 'CNY' as Cnt, Cast('2020-01-01' as date) as StartDate,Cast(4.3100 as decimal(6,4)) as Rate 
    union
    Select 'NT' , 'CNY' , Cast('2020-02-01' as date) ,Cast(4.3200 as decimal(6,4))
    union
    Select 'NT' , 'CNY' , Cast('2020-02-10' as date) ,Cast(4.3300 as decimal(6,4))
    union
    Select 'NT' , 'US' , Cast('2020-01-01' as date) ,Cast(30.2100 as decimal(6,4))
    union
    Select 'NT' , 'US' , Cast('2020-03-01' as date) ,Cast(30.1100 as decimal(6,4))
    union
    Select 'NT' , 'CNY' , Cast('2020-03-05' as date) ,Cast(4.5300 as decimal(6,4))
    union
    Select 'NT' , 'US' , Cast('2020-04-05' as date) ,Cast(29.3100 as decimal(6,4))
)
, CurrRateRange
as
(
    Select *
    ,(LEAD(StartDate) OVER ( PARTITION BY RefCnt, Cnt ORDER BY StartDate )) AS EndDate
    From CurrRate
)
Select 
    RefCnt, Cnt, StartDate,
    DATEADD(DAY, -1, EndDate) as EndDate,
    Rate
From CurrRateRange;