Sql-Server

連續日期範圍 - 差距和島嶼

  • April 29, 2019
CREATE TABLE T1
   (Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME)
;
INSERT INTO T1
   (Asset_Id, Trailer_Id, AssignStart, AssignEnd)
VALUES
   (37124, 32607, '2018-04-19 08:55:00', '2018-05-05 10:00:00.000'),
   (37124, 32607, '2018-05-05 11:23:00', NULL),
   (33000, 30000, '2018-04-01 15:00:00', '2018-04-15 10:30:00.000'),
   (34000, 31000, '2018-04-05 10:00:00', '2018-04-10 09:30:00.000'),
   (34000, 32500, '2018-04-10 09:31:00', NULL),
   (37000, 32600, '2018-04-19 08:55:00', '2018-04-25 08:30:00.000'),
   (37000, 32600, '2018-04-25 09:23:00', '2018-04-25 10:00:00.000'),
   (37000, 32600, '2018-04-25 11:23:00', '2018-04-30 15:00:00.000'),
   (37000, 32600, '2018-04-30 16:15:00', '2018-04-30 17:30:00.000'),
   (37000, 32600, '2018-05-01 18:23:00', NULL),
   (38000, 36000, '2018-05-01 10:00:00', '2018-05-10 06:30:00.000'),
   (38000, 36000, '2018-05-15 09:00:00', '2018-05-20 11:00:00.000'),
   (38000, 36000, '2018-05-20 12:00:00', NULL),
   (33000, 30000, '2018-05-01 10:00:00', NULL)
;

我有以下範例數據 -

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37124       32607       2018-04-19 08:55:00.000 2018-05-05 10:00:00.000
37124       32607       2018-05-05 11:23:00.000 NULL
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000       31000       2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000       32500       2018-04-10 09:31:00.000 NULL
37000       32600       2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000       32600       2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000       32600       2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000       32600       2018-04-30 16:15:00     2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00     NULL
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000       36000       2018-05-20 12:00:00.000 NULL
33000       30000       2018-05-01 10:00:00.000 NULL

如您所見，資產和預告片之間的一些分配在同一天結束並再次開始 - 除了最後一行 - 差距和島嶼

範例（1）

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37000       32600       2018-04-19 08:55:00.000 2018-04-25 08:30:00.000
37000       32600       2018-04-25 09:23:00.000 2018-04-25 10:00:00.000
37000       32600       2018-04-25 11:23:00.000 2018-04-30 15:00:00.000
37000       32600       2018-04-30 16:15:00.000 2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00.000 NULL

我期待的這個樣本的輸出是

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37000       32600       2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00.000 NULL

現在這是另一部分與間隙和島嶼。相同資產和預告片之間的某些分配已結束，然後在將來的某個日期重新開始

範例（2）

Asset_Id    Trailer_Id  AssignStart             AssignEnd
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000       30000       2018-05-01 10:00:00.000 NULL

我期待的這個樣本的輸出是

Asset_Id    Trailer_Id  AssignStart             AssignEnd
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
33000       30000       2018-05-01 10:00:00.000 NULL

範例（3）

Asset_Id    Trailer_Id  AssignStart             AssignEnd
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 2018-05-20 11:00:00.000
38000       36000       2018-05-20 12:00:00.000 NULL

我期待的這個樣本的輸出是

Asset_Id    Trailer_Id  AssignStart             AssignEnd
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 NULL

我努力編寫將提供以下輸出的查詢

Asset_Id    Trailer_Id  AssignStart             AssignEnd
37124       32607       2018-04-19 08:55:00.000 NULL
33000       30000       2018-04-01 15:00:00.000 2018-04-15 10:30:00.000
34000       31000       2018-04-05 10:00:00.000 2018-04-10 09:30:00.000
34000       32500       2018-04-10 09:31:00.000 NULL
37000       32600       2018-04-19 08:55:00.000 2018-04-30 17:30:00.000
37000       32600       2018-05-01 18:23:00.000 NULL
38000       36000       2018-05-01 10:00:00.000 2018-05-10 06:30:00.000
38000       36000       2018-05-15 09:00:00.000 NULL
33000       30000       2018-05-01 10:00:00.000 NULL

使用this SO answer中的答案作為基礎，這是我的嘗試，它不太正確

您可以在 CTE 的幫助下使用遞歸方法。為此，我添加了一個Row_Numberso ，以確保它是驗證的下一條記錄。

然後創建遞歸 CTE…命名cte。在這裡，欄位很重要rn as rn_init——這是創建記錄鏈的基礎。

;
WITH fr AS -- first records
(
SELECT [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]
 ,ROW_NUMBER()OVER( PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY 
  [AssignStart], [AssignEnd]) AS rn
FROM  T1
)
,cte AS
(
SELECT [Asset_Id], [Trailer_Id]
     , [AssignStart] as [AssignStart_init] , [AssignEnd] as [AssignEnd_Init] 
     , [AssignStart] as [AssignStart],  [AssignEnd] as [AssignEnd]
     , 0 as lvl
     , rn as rn
     , rn as rn_init
FROM fr
--WHERE [AssignEnd] is not null

UNION ALL

SELECT 
    T1.[Asset_Id], T1.[Trailer_Id]
    ,C.[AssignStart_init],C.[AssignEnd_Init]
    ,T1.[AssignStart], T1.[AssignEnd]
    ,C.lvl + 1 
    ,T1.rn
    ,C.rn_init
FROM fr AS T1
    INNER JOIN cte as C
       ON T1.[Asset_Id] = C.[Asset_Id]
       AND T1.[Trailer_Id] = C.[Trailer_Id]
       AND CONVERT(DATE,C.[AssignEnd]) = CONVERT(DATE,T1.[AssignStart])
       AND C.[AssignEnd] <= T1.[AssignStart]
       AND C.rn + 1 = T1.rn
)

這里處理NULL的是 AssignEnd 的情況，也很重要，要計算“記錄鏈” count(rn_init) as c_rn_init。基於此，稍後對其進行過濾。

--select * from cte
,myPrecious AS
(
select  --C.* 
  C.[Asset_Id],c.[Trailer_Id]
   ,MIN([AssignStart]) as AssignStart
   --,MAX([AssignEnd]) as AssignEnd
   ,CASE WHEN MAX(isnull([AssignEnd],[AssignStart])) = MAX([AssignStart]) THEN NULL ELSE MAX(isnull([AssignEnd],[AssignStart])) END as [AssignEnd]
   ,count(rn_init) as c_rn_init
from cte as c
group by C.[Asset_Id],c.[Trailer_Id], C.rn_init

)

這如果最終查詢。添加了 Row_number ，所以要過濾掉記錄，ORDER BY c_rn_init DESC

SELECT 
 Asset_Id,Trailer_Id,AssignStart,AssignEnd
 --,c_rn_init
 --,rn
 FROM(
         SELECT * 
              ,row_number() OVER(PARTITION BY [Asset_Id],[Trailer_Id] , AssignEnd ORDER BY c_rn_init DESC) as rn
         FROM myPrecious
     )A
WHERE A.rn = 1
order by [Asset_Id],[Trailer_Id]  ,[AssignStart],[AssignEnd]

輸出：

Asset_Id    Trailer_Id  AssignStart         AssignEnd
33000       30000       01/04/2018 15:00:00 15/04/2018 10:30:00
33000       30000       01/05/2018 10:00:00 null
34000       31000       05/04/2018 10:00:00 10/04/2018 09:30:00
34000       32500       10/04/2018 09:31:00 null
37000       32600       19/04/2018 08:55:00 30/04/2018 17:30:00
37000       32600       01/05/2018 18:23:00 null
37124       32607       19/04/2018 08:55:00 null
38000       36000       01/05/2018 10:00:00 10/05/2018 06:30:00
38000       36000       15/05/2018 09:00:00 null

小提琴手

引用自：https://dba.stackexchange.com/questions/209636

相關問答

Sql-Server

如何合併具有不同有效期的相似記錄？

  • February 16, 2018
檢視問答
Sql-Server

在有序集中對具有空值的行的子集進行分組

  • November 22, 2017
檢視問答
Sql-Server

根據之前的月末值填充缺失數據

  • August 16, 2017
檢視問答
Sql-Server

有什麼方法可以在沒有游標的情況下計算值的頻率？

  • July 20, 2017
檢視問答
Sql-Server

查詢以獲取每個組的隊列位置

  • March 11, 2016
檢視問答
Sql-Server

計算總訪問次數

  • February 24, 2016
檢視問答