在存储在Massive Varchar字段中的EventLog中计算持续时间的最有效方法是什么

Question

我们公司在SQL表的VarChar字段中记录到不同代理的来电。此字段为EventLog。

因此，Select EventLog From DB Where CallID = 'Long_String_of_Numbers'在一个LONG行中产生以下内容：

21:02:10: Initializing 21:02:10: Offering 21:02:10: ANI:  5551234567 21:02:10: DNIS:  1032 21:02:10: Call answered 21:02:10: Call from: 5551234567 21:02:12: Offering 21:02:12: Entered Workgroup Clients 21:02:20: ACD - Wait Agent 21:02:20: ACD - Wait Agent 21:02:20: ACD - Wait Agent 21:02:20: ACD call waiting for agent 21:05:47: ACD interaction assigned to arnoldSW 21:05:47: ACD call waiting for agent 21:05:47: ACD - Alerting: arnoldSW 21:05:48: Offering 21:05:48: Sent to user arnoldSW 21:05:48: Alerting 21:05:48: Sent to station WOLF1 21:05:51: Connected 21:05:51: ACD interaction connected to arnoldSW 21:05:51: ACD - Assigned: arnoldSW 21:15:34: Sent to station ABC 21:16:42: Held 21:17:19: Connected 21:17:19: Sent to user northSK 21:20:04: Disconnected [Local Hang Up]

为了简单起见，这里有一行格式化的字符串：

21:02:10: Initializing 
21:02:10: Offering 
21:02:10: ANI:  5551234567 
21:02:10: DNIS:  1032 
21:02:10: Call answered 
21:02:10: Call from: 5551234567 
21:02:12: Offering 
21:02:12: Entered Workgroup Clients 
21:02:20: ACD - Wait Agent 
21:02:20: ACD - Wait Agent 
21:02:20: ACD - Wait Agent 
21:02:20: ACD call waiting for agent 
21:05:47: ACD interaction assigned to arnoldSW 
21:05:47: ACD call waiting for agent 
21:05:47: ACD - Alerting: arnoldSW 
21:05:48: Offering 
21:05:48: Sent to user arnoldSW 
21:05:48: Alerting 
21:05:48: Sent to station WOLF1 
21:05:51: Connected 
21:05:51: ACD interaction connected to arnoldSW 
21:05:51: ACD - Assigned: arnoldSW                 // Duration From HERE ...
21:15:34: Sent to station ABC 
21:16:42: Held 
21:17:19: Connected 
21:17:19: Sent to user northSK 
21:20:04: Disconnected [Local Hang Up]             // ... To Here

计算ACD - Assigned和Disconnected之间的持续时间（以分钟为单位）的最有效方法是什么？（注意：在这些事件之前、之间和之后记录的步骤数各不相同，我需要为至少19000个调用执行此操作。因此，这是构建统计信息的有效方法。）

Answer 1

很多问题，比如这个问题，都归结为糟糕的模式。尽管如此，如果你不能改变它，那么你可以做一些类似于下面的事情。它搜索字段名称，备份适当数量的字符，并将时间转换为实际的时间数据类型，以便使用DATEDIFF计算秒数。我把它留到了秒，因为你没有指定如何四舍五入到分钟，但转换到分钟已经足够简单了。

DECLARE @test varchar(2048)
SET @test = '21:02:10: Initializing 21:02:10: Offering 21:02:10: ANI:  5551234567 21:02:10: DNIS:  1032 21:02:10: Call answered 21:02:10: Call from: 5551234567 21:02:12: Offering 21:02:12: Entered Workgroup Clients 21:02:20: ACD - Wait Agent 21:02:20: ACD - Wait Agent 21:02:20: ACD - Wait Agent 21:02:20: ACD call waiting for agent 21:05:47: ACD interaction assigned to arnoldSW 21:05:47: ACD call waiting for agent 21:05:47: ACD - Alerting: arnoldSW 21:05:48: Offering 21:05:48: Sent to user arnoldSW 21:05:48: Alerting 21:05:48: Sent to station WOLF1 21:05:51: Connected 21:05:51: ACD interaction connected to arnoldSW 21:05:51: ACD - Assigned: arnoldSW 21:15:34: Sent to station ABC 21:16:42: Held 21:17:19: Connected 21:17:19: Sent to user northSK 21:20:04: Disconnected [Local Hang Up]'
SELECT
    CAST(SUBSTRING(@test, PATINDEX('%ACD - Assigned:%', @test) - 10, 8) AS time) AS AssignedTime,
    CAST(SUBSTRING(@test, PATINDEX('%Disconnected%', @test) - 10, 8) AS time) AS DisconnectTime,
    DATEDIFF(second, 
        CAST(SUBSTRING(@test, PATINDEX('%ACD - Assigned:%', @test) - 10, 8) AS time),
        CAST(SUBSTRING(@test, PATINDEX('%Disconnected%', @test) - 10, 8) AS time)) AS SecondsElapsed