我想找到所有連續日期范圍的結束日期。有些日期不是連續的，在這種情況下，它將回傳單個范圍的結尾。
Table Name: Sospensioni
ClientId. Status. StartDate EndDate
1 1 01/01/2022 02/01/2022
1 1 03/01/2022 04/01/2022
1 1 12/01/2022 15/01/2022
2 1 03/01/2022 03/01/2022
2 1 05/01/2022 06/01/2022
我想要一個 sql 陳述句來合并每個客戶端的連續范圍（結果示例）
ClientId. Status. StartDate EndDate
1 1 01/01/2022 04/01/2022
1 1 12/01/2022 15/01/2022
2 1 03/01/2022 03/01/2022
2 1 05/01/2022 06/01/2022
我只想使用 SQL 來解決問題。謝謝
uj5u.com熱心網友回復：
這是一個差距和島嶼問題。您可以使用典型的解決方案LAG()。例如：
select
max(client_id) as client_id,
max(status) as status,
min(start_date) as start_date,
max(end_date) as end_date
from (
select *, sum(i) over(partition by client_id order by start_date) as g
from (
select *,
case when dateadd(day, -1, start_date) <>
lag(end_date) over(partition by client_id order by start_date)
then 1 else 0 end as i
from t
) x
) y
group by client_id, g
order by client_id, g
結果：
client_id status start_date end_date
---------- ------- ----------- ----------
1 1 2022-01-01 2022-01-04
1 1 2022-01-12 2022-01-15
2 1 2022-01-03 2022-01-03
2 1 2022-01-05 2022-01-06
請參閱db<>fiddle的運行示例。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/487000.html