我正在研究一個 SQL Server 資料庫，試圖根據有效日期拆分記錄，現在正在研究如何拆分它們。
我有 2 個表： Subscription 和 Effective_Rates 如下：
訂閱
ID | Date_From | Date_To | Cost
------ -------------- -------------- ------------
1001 | 2020-01-01 | 2020-12-31 | 2000.00
1002 | 2020-05-01 | 2021-04-30 | 3500.00
Effective_Rates
ID | Effective_From | Rate
------ ------------------- --------
1001 | 2020-01-01 | 10.0
1002 | 2020-08-01 | 12.0
1003 | 2021-01-01 | 15.0
我需要加入這兩個表并根據生效日期按比率拆分記錄并查找結果如下：
ID | Date_From | Date_To | Cost | Rate
------ -------------- -------------- ------------- --------
1001 | 2020-01-01 | 2020-07-31 | 2000.00 | 10.0
1001 | 2020-08-01 | 2020-12-31 | 2000.00 | 12.0
1002 | 2020-05-01 | 2020-07-31 | 3500.00 | 10.0
1002 | 2020-08-01 | 2020-12-31 | 3500.00 | 12.0
1002 | 2021-01-01 | 2021-04-30 | 3500.00 | 15.0
再次感謝您的幫助
uj5u.com熱心網友回復：
WITH SUBSCRIPTION(ID,DATE_FROM,DATE_TO,COST) AS
(
SELECT 1001 , '2020-01-01' , '2020-12-31' , 2000.00 UNION ALL
SELECT 1002 , '2020-05-01' , '2021-04-31' , 3500.00
),
Effective_Rates(ID , Effective_From, Rate )AS
(
SELECT 1001 , '2020-01-01' , 10.0 UNION ALL
SELECT 1002 , '2020-08-01' , 12.0 UNION ALL
SELECT 1003 , '2021-01-01' , 15.0
)
SELECT S.ID,S.DATE_FROM,S.DATE_TO,S.COST,X.Rate,X.Effective_From
FROM SUBSCRIPTION AS S
CROSS APPLY
(
SELECT E.RATE,E.Effective_From
FROM Effective_Rates AS E
WHERE E.Effective_From BETWEEN S.DATE_FROM AND S.DATE_TO
)X
ORDER BY S.ID;
恐怕不完全適合，但希望能有用
uj5u.com熱心網友回復：
區間 a 和 b 的交集條件是a.start<=b.end and b.start<=a.end。因此可以通過查詢找到感興趣的區間
with eri as(
select ID, Effective_From, Rate
, dateadd(dd, -1, lead(Effective_From) over(order by Effective_From) ) eff_till
from Effective_Rates
)
SELECT S.ID
, case when S.DATE_FROM <= eri.Effective_From then eri.Effective_From else S.DATE_FROM end d_from
, case when S.DATE_TO <= eri.eff_till or eri.eff_till is null then S.DATE_TO else eri.eff_till end d_to
, S.COST, eri.Rate, eri.Effective_From
FROM SUBSCRIPTION AS S
JOIN eri on eri.Effective_From <= S.DATE_TO and (s.DATE_FROM < eri.eff_till or eri.eff_till is null)
ORDER BY S.ID, d_from;
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/422524.html
標籤：
上一篇：我能知道這個SQL查詢的問題嗎