我正在嘗試根據表中的日期列計算 StartDate 和 EndDate。下面是源表的樣子
場景一
| ID | 序列號 | 地位 | 讀取時間 |
|---|---|---|---|
| 123456789 | 42007 | D | 15-12-2021 |
| 123456789 | 42007 | D | 16-12-2021 |
| 123456789 | 42007 | D | 17-12-2021 |
| 123456789 | 42007 | D | 18-12-2021 |
| 123456789 | 42007 | D | 19-12-2021 |
| 123456789 | 42007 | D | 20-12-2021 |
| 123456789 | 42007 | D | 21-12-2021 |
我想根據 READ_DT 計算 start_date 和 end_date，對于 ID 和 SERIAL_NUMBER，如果所有 READ_DT 都可用，則輸出應如下所示
| ID | 序列號 | 地位 | 開始日期 | 結束日期 |
|---|---|---|---|---|
| 123456789 | 42007 | D | 15-12-2021 | 21-12-2021 |
場景二
| ID | 序列號 | 地位 | 讀取時間 |
|---|---|---|---|
| 123456789 | 42007 | D | 15-12-2021 |
| 123456789 | 42007 | D | 16-12-2021 |
| 123456789 | 42007 | D | 17-12-2021 |
| 123456789 | 42007 | D | 19-12-2021 |
| 123456789 | 42007 | D | 20-12-2021 |
| 123456789 | 42007 | D | 21-12-2021 |
如果 READ_DT 之間存在任何間隙，則預期輸出應在兩個事務中，如下所示。
| ID | 序列號 | 地位 | 開始日期 | 結束日期 |
|---|---|---|---|---|
| 123456789 | 42007 | D | 15-12-2021 | 17-12-2021 |
| 123456789 | 42007 | D | 19-12-2021 | 21-12-2021 |
uj5u.com熱心網友回復：
一點順序時間數學可以使這些事情變得簡短。
--===== This will work for either scenario
WITH cteDTgrp AS
(--==== Subtract an increasing number of days from each date to create the date groups.
SELECT *
,DT_Grp = DATEADD(dd,-ROW_NUMBER() OVER (PARTITION BY ID,SERIAL_NUMBER,STATUS ORDER BY READ_DT),READ_DT)
FROM dbo.YourTableNameHere
)--==== Then the grouping to get the start and end dates is trivial.
SELECT ID,SERIAL_NUMBER,STATUS
,Start_Date = MIN(READ_DT)
,End_Date = MAX(READ_DT)
FROM cteDTgrp
GROUP BY ID,SERIAL_NUMBER,STATUS,DT_Grp --<----This is the key!
ORDER BY ID,SERIAL_NUMBER,STATUS,Start_Date
;
請注意，這僅在 READ_DT 對于每組 ID、SERIAL_NUMBER、STATUS 是唯一的情況下才有效。
uj5u.com熱心網友回復：
對于場景 1，您可以通過剩余的列直接使用聚合最小和最大函陣列。
select ID,SERIAL_NUMBER, STATUS, convert(varchar, min(READ_DT), 105) as Start_Date, convert(varchar, max(READ_DT), 105) as End_Date
from tb1
group by ID,SERIAL_NUMBER, STATUS
對于場景 2，我使用 LAG 函式獲取當前行與前一行的日期差異，然后執行聚合。
此代碼適用于場景 1 和 2 資料。
代碼：
drop table if exists #t1
--stores diff_days and missing date from sequence
SELECT READ_DT,
case when DATEDIFF(day, LAG(READ_DT) OVER (ORDER BY READ_DT), READ_DT ) is NULL then 1
else DATEDIFF(day, LAG(READ_DT) OVER (ORDER BY READ_DT), READ_DT )
end AS diff_day
,case when DATEDIFF(day, LAG(READ_DT) OVER (ORDER BY READ_DT), READ_DT ) >1 then DATEADD(day, -1, READ_DT)
end as diff_read_dt
into #t1
from tb2
--update diff_day column where date greater that missing date to aggregate on the result set
update #t1
set diff_day = diff_day 1
where convert(date,READ_DT) > (select dateadd(day,1,convert(date,diff_read_dt)) from #t1 where diff_read_dt is not null)
--get the required results using min and max
select a.ID, a.SERIAL_NUMBER, a.STATUS, convert(varchar, min(a.READ_DT), 105) as Start_Date, convert(varchar, max(a.READ_DT), 105) as End_Date
from tb2 a
inner join #t1 b on convert(date,a.READ_DT) = convert(date,b.READ_DT)
group by a.ID, a.SERIAL_NUMBER, a.STATUS, b.diff_day
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/390539.html