代码之家 › 专栏 › 技术社区 › Jhonny D. Cano -Leftware-

事件表的基于集合的计算,事件除外

set-based sql-server-2000 tsql sql-server sql

0

Jhonny D. Cano -Leftware- · 技术社区 · 15 年前

我有一个表,其中包含更改维护服务状态的事件。这是一个数据示例:

id  date    event
234 2009-04-22 10:00:00 Service Request
234 2009-04-22 10:02:00 Service Approbation
234 2009-04-22 10:03:00 Service Asignation
234 2009-04-22 10:40:00 Service Fulfilled
...

在报告中,我需要显示在每个项目上花费的时间

id  time
234 40 minutes
235 37 minutes
...

基本上,我在做这样的DateDiff:

SELECT ...
, DATEDIFF(SECOND, 
(SELECT TOP 1 date FROM event_table ev WHERE ev.item = itm.id AND event = 1 ORDER BY date ), 
(SELECT TOP 1 date  FROM event_table ev WHERE ev.item = itm.id AND event = 4 ORDER BY date DESC)
        ), ...
FROM item_table itm
WHERE ...

现在我必须实施一个新的国家。服务的延迟和重新激活,但在计算在该服务上花费的总时间量时,不必考虑这些时间。

id  date    event
234 2009-04-22 10:00:00 Service Request
234 2009-04-22 10:02:00 Service Approbation
234 2009-04-22 10:03:00 Service Asignation
234 2009-04-22 10:07:00 Service Delayed           -- new state
234 2009-04-22 10:37:00 Service Reactivated       -- new state
234 2009-04-22 10:40:00 Service Fulfilled
...

报告

id  time
234 10 minutes  -- Substract 30 minutes between Delay and Reactivation
235 26 minutes
...

任何项目的延迟和重新激活都可能发生不同的时间。

我知道我可以用一个光标来计算,但对我来说,这似乎很难,有没有办法用基于集合的方法来计算呢?

1 回复 | 直到 15 年前

1

MatBailie 15 年前

我要做的是首先找出整个工作的持续时间,减去所有延迟的持续时间…

查找延迟持续时间的代码:

SELECT
   [delay_start].event_id,
   SUM(DATEDIFF(second, [delay_start].date, [delay_finish].date))
FROM
   event_table AS [delay_start]
INNER JOIN
   event_table AS [delay_finish]
      ON  [delay_finish].event_id = [delay_start].event_id
      AND [delay_finish].date = (
                                 SELECT
                                    MIN(date)
                                 FROM
                                    event_table
                                 WHERE
                                    event_id = [delay_start].event_id
                                    AND date > [delay_start].date
                                    AND event = 'Service Reactivated'
                                )
WHERE
   [delay_start].event = 'Service Delayed'
   AND [delay_finish].event = 'Service Reactivated'
GROUP BY
   [delay_start].event_id

编辑 :更正了几个拼写错误…