Sql-Server-2008

獲取日誌條目之間經過的時間

  • January 14, 2021

在一家公司，按照準備/配方准備液體並將其保存在罐中。準備工作根據需要從一個罐子轉移到另一個罐子。任務按順序記錄到表中。

我需要獲取的是每個配方在每個罐中花費的時間，從第 1 步到第 X 步。問題是我沒有直接的方法來辨識同一配方的不同批次，也沒有明確的“ END”日誌條目。

我確信有一種方法可以通過 CTE 實現它，但我似乎無法理解它。

我可以確定的事情：

  • 配方總是從罐中的第 1 步開始。
  • 坦克一次只能容納一個配方。
  • ID 不會是連續的。
  • 步數可以並且將是可變的。

樣本數據：

ID      Rec_Number  tank  Step_Number  Start_Ts             End_Ts
134754  R8020       RECP  1            2015-03-16 05:40:00  2015-03-16 05:40:00
134755  R8020       RECP  2            2015-03-16 05:40:00  2015-03-16 05:48:00
134756  R8020       RECP  3            2015-03-16 05:48:00  2015-03-16 05:52:00
134757  R8020       RECP  4            2015-03-16 05:52:00  2015-03-16 05:57:00
134758  R8020       RECP  5            2015-03-16 05:57:00  2015-03-16 06:01:00
134759  R8020       RECP  6            2015-03-16 06:01:00  2015-03-16 06:02:00
134760  R8020       RECP  7            2015-03-16 06:02:00  2015-03-16 06:02:00
134761  R8020       RECP  8            2015-03-16 06:02:00  2015-03-16 06:03:00
134762  R8020       RECP  9            2015-03-16 06:03:00  2015-03-16 06:03:00
134763  R8020       RECP  10           2015-03-16 06:03:00  2015-03-16 06:03:00
134764  R8020       RECP  11           2015-03-16 06:03:00  2015-03-16 06:03:00
134765  R8020       RECP  12           2015-03-16 06:03:00  2015-03-16 06:06:00
134766  R8020       RECP  13           2015-03-16 06:06:00  2015-03-16 06:07:00
134767  R8020       RECP  14           2015-03-16 06:07:00  2015-03-16 06:07:00
134769  R8020       RECP  15           2015-03-16 06:07:00  2015-03-16 06:07:00
134778  R8020       RE01  1            2015-03-16 06:11:00  2015-03-16 06:12:00
134779  R8020       RE01  2            2015-03-16 06:12:00  2015-03-16 06:13:00
134780  R8020       RE01  3            2015-03-16 06:13:00  2015-03-16 06:13:00
134781  R8020       RE01  4            2015-03-16 06:13:00  2015-03-16 06:13:00
134782  R8020       RE01  5            2015-03-16 06:13:00  2015-03-16 06:14:00
134784  R8020       RE01  6            2015-03-16 06:14:00  2015-03-16 06:14:00
134785  R8020       RE01  7            2015-03-16 06:14:00  2015-03-16 06:15:00
134786  R8020       RE01  8            2015-03-16 06:15:00  2015-03-16 06:16:00
134788  R8020       RECP  1            2015-03-16 06:16:00  2015-03-16 06:16:00
134789  R8020       RECP  2            2015-03-16 06:16:00  2015-03-16 06:18:00
134803  R8020       RE01  9            2015-03-16 06:16:00  2015-03-16 06:48:00
134790  R8020       RECP  3            2015-03-16 06:18:00  2015-03-16 06:21:00
134791  R8020       RECP  4            2015-03-16 06:21:00  2015-03-16 06:25:00
134792  R8020       RECP  5            2015-03-16 06:25:00  2015-03-16 06:26:00
134793  R8020       RECP  6            2015-03-16 06:26:00  2015-03-16 06:27:00
134794  R8020       RECP  7            2015-03-16 06:27:00  2015-03-16 06:27:00
134795  R8020       RECP  8            2015-03-16 06:27:00  2015-03-16 06:28:00
134796  R8020       RECP  9            2015-03-16 06:28:00  2015-03-16 06:28:00
134797  R8020       RECP  10           2015-03-16 06:28:00  2015-03-16 06:28:00
134798  R8020       RECP  11           2015-03-16 06:28:00  2015-03-16 06:29:00
134799  R8020       RECP  12           2015-03-16 06:29:00  2015-03-16 06:32:00
134800  R8020       RECP  13           2015-03-16 06:32:00  2015-03-16 06:34:00
134802  R8020       RECP  14           2015-03-16 06:34:00  2015-03-16 06:34:00
134804  R8020       RE01  10           2015-03-16 06:48:00  2015-03-16 07:03:00
134805  R8020       RE01  11           2015-03-16 07:03:00  2015-03-16 07:03:00
134806  R8020       RE01  12           2015-03-16 07:03:00  2015-03-16 07:03:00
134808  R8020       RE01  13           2015-03-16 07:03:00  2015-03-16 07:07:00
134811  R8020       RE01  1            2015-03-16 07:07:00  2015-03-16 07:09:00
134812  R8020       RE01  2            2015-03-16 07:09:00  2015-03-16 07:09:00
134813  R8020       RE01  3            2015-03-16 07:09:00  2015-03-16 07:09:00
134814  R8020       RE01  4            2015-03-16 07:09:00  2015-03-16 07:09:00
134815  R8020       RE01  5            2015-03-16 07:09:00  2015-03-16 07:10:00
134817  R8020       RE01  6            2015-03-16 07:10:00  2015-03-16 07:10:00
134820  R8020       RE01  7            2015-03-16 07:10:00  2015-03-16 07:12:00
134821  R8020       RE01  8            2015-03-16 07:12:00  2015-03-16 07:14:00
134823  R8020       RECP  2            2015-03-16 07:12:00  2015-03-16 07:44:00
134819  R8020       RECP  1            2015-03-16 07:12:00  2015-03-16 07:12:00
134822  R8020       RE01  9            2015-03-16 07:14:00  2015-03-16 07:36:00
134826  R8020       RE01  10           2015-03-16 07:36:00  2015-03-16 07:51:00
134824  R8020       RECP  3            2015-03-16 07:44:00  2015-03-16 07:48:00
134825  R8020       RECP  4            2015-03-16 07:48:00  2015-03-16 07:50:00
134827  R8020       RECP  5            2015-03-16 07:50:00  2015-03-16 07:51:00
134828  R8020       RECP  6            2015-03-16 07:51:00  2015-03-16 07:52:00

所以配方 R8020 從罐 RECP 開始 15 步，然後移動到 RE01 13 步。同時，一個新的 R8020 在罐 RECP 中啟動 14 步。等等。

我想擁有：

Recipe  Tank  TimeSpentInTank
R8020   RECP  00:27:00
R8020   RE01  00:23:00
R8020   RECP  00:18:00
etc.

我在 SQL 2008 中。解決方案可以包括任意數量的步驟，包括視圖、CTE、透視或儲存過程。

不知道你的表名，所以我創建了自己的名字。更改表名，它應該可以工作。這以分鐘為單位給出了差異。

select A.rec_number,A.tank,
CONVERT(varchar(5), DATEADD(minute, datediff (minute,A.start_ts,B.end_ts), 0), 114)   as differenceTime from
(
select a.rec_number,a.tank, start_ts from dbo.TEST a
inner join(
select MIN(step_number) as minstep,rec_number,tank from dbo.TEST
group by rec_number,tank)b on b.minstep = a.step_number and b.rec_number = a.rec_number and b.tank = a.tank
)A
INNER JOIN
(
select a.rec_number,a.tank,end_ts from dbo.TEST a
inner join(
select MAX(step_number) as maxstep,rec_number,tank from dbo.TEST
group by rec_number,tank)b on b.maxstep = a.step_number and b.rec_number = a.rec_number and b.tank = a.tank
)B
ON A.rec_number =B.rec_number and A.tank = B.tank

我終於設法解決了我的問題。

我首先為每個“步驟 1”添加一個唯一 ID，然後使用 OUTER APPLY 以低於它的 START_TS 填充所有內容的其餘部分。

-- Add a unique ID to every Step 1
DECLARE @myVar INT = 0
UPDATE #start SET @myvar = JobID = @myVar + 1 WHERE Step_Number=1

-- Fill in all the blanks to group with the new ID
UPDATE  #start
   SET     JobID = T3.JobID
   FROM    #start T1
       OUTER APPLY
       (   SELECT  MAX(JobID) JobID
           FROM    #start T2
           WHERE T2.Start_Ts < T1.Start_Ts
               AND t2.Step_Number=1
               AND T2.Rec_Number=t1.Rec_Number AND t2.Tank=t1.Tank
       ) T3
   WHERE T1.jobid IS NULL

-- Final Selection
SELECT JobID, rec_number, tank, MIN(start_ts) DTSTART, MAX(end_ts) DTEND, DATEDIFF(MINUTE,MIN(start_ts),MAX(end_ts)) Temps 
   FROM #start
   WHERE Rec_Number <> '' AND tank <> '' AND Step_Number <> '' AND jobid IS NOT NULL
   GROUP BY JobID, rec_number, tank
   ORDER BY rec_number, tank, MIN(start_ts)

感謝所有試一試的人！

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

相關問答

Sql-Server

如何檢查 null 或空表值參數？

  • March 2, 2022
檢視問答
Sql-Server

如果為正，則對所有項目求和。如果為負，則返回每一個

  • January 18, 2022
檢視問答
Sql-Server

唯一列是否可以包含多個空值 SQL Server 2008

  • December 7, 2021
檢視問答
Sql-Server

為什麼 GROUP BY 語句中的萬用字元不起作用？

  • October 22, 2021
檢視問答
Sql-Server

單獨的特定索引 VS 單一的包容性索引

  • August 10, 2021
檢視問答
Sql-Server-2008

科目滿分的學生

  • April 20, 2021
檢視問答