我有桌子
TABLE [dbo].[IssueStatus](
[Id] [int] PRIMARY KEY ,
[IssueId] [varchar(50)],
[OldStatus] [int] ,
[NewStatus] [int] ,
[Updated] [datetime]
2个状态列可以具有3个可能的值1、2和3（1表示打开，2表示进行中，3表示已解决）。
Updated包含状态更改时的日期时间。问题的状态最初会自动设置为1。我想计算问题进行时的总时间（以秒为单位）。
注意：-状态可能直接从1更改为3。
状态可以从1更改为2，然后再更改为1，依此类推，但最终状态可以保证为3
我已经检查过-根据日期时间计算表中不同记录之间的持续时间，但是对我没有多大帮助
原始情况要复杂得多：1处于打开状态，3处于进行中，4处于重新打开状态，5处于解决状态，6处于关闭状态
谢谢你
79890 26327 3 In Progress 5 Resolved 2014-12-17 09:10:03.767
74980 26328 3 In Progress 5 Resolved 2014-11-20 10:21:29.780
74748 26328 1 Open 3 In Progress 2014-11-20 02:34:15.440
77843 26329 1 Open 3 In Progress 2014-12-08 08:04:04.567
77857 26329 1 Open 5 Resolved 2014-12-08 08:23:57.720
77856 26329 3 In Progress 1 Open 2014-12-08 08:23:46.067
75107 26330 1 Open 5 Resolved 2014-11-21 06:37:28.810
76441 26330 5 Resolved 6 Closed 2014-12-02 07:27:39.927
78638 26331 1 Open 3 In Progress 2014-12-10 07:47:41.347
78091 26331 3 In Progress 1 Open 2014-12-09 02:44:36.970
77858 26331 1 Open 3 In Progress 2014-12-08 08:28:08.597
78641 26331 3 In Progress 1 Open 2014-12-10 07:57:03.603
78642 26331 1 Open 5 Resolved 2014-12-10 07:57:11.483
74753 26332 1 Open 3 In Progress 2014-11-20 02:59:11.013
74763 26332 3 In Progress 5 Resolved 2014-11-20 03:04:01.127
76846 26333 1 Open 5 Resolved 2014-12-05 00:57:09.140
76849 26340 1 Open 5 Resolved 2014-12-05 01:52:05.957
87861 26341 5 Resolved 6 Closed 2015-02-02 04:18:25.230
85491 26341 1 Open 5 Resolved 2015-01-22 04:48:13.003
77321 26342 3 In Progress 1 Open 2014-12-08 00:56:26.233
75029 26342 1 Open 3 In Progress 2014-11-21 02:48:41.440
79030 26342 3 In Progress 5 Resolved 2014-12-11 21:43:23.657
76395 26342 1 Open 3 In Progress 2014-12-02 02:58:17.063
75197 26342 3 In Progress 1 Open 2014-11-24 02:06:38.490
78502 26342 1 Open 3 In Progress 2014-12-10 02:28:18.570
74933 26343 1 Open 5 Resolved 2014-11-20 08:08:44.423
74821 26344 1 Open 5 Resolved 2014-11-20 05:56:00.513
75295 26345 1 Open 5 Resolved 2014-11-25 02:06:07.260
我的下面查询显示每个条目处于状态2的时间。
使用以下示例数据：
Id IssueId OldStatus NewStatus Updated
1 aa NULL 1 2015-01-01 14:00:00
2 aa 1 2 2015-01-01 16:00:00
4 aa 2 3 2015-01-01 17:30:00
5 bb NULL 1 2015-02-13 11:30:00
6 bb 1 2 2015-02-13 12:56:00
7 bb 2 3 2015-02-13 14:20:00
8 cc NULL 1 2015-02-14 11:30:00
9 cc 1 2 2015-02-14 12:56:00
10 cc 2 1 2015-02-14 13:19:00
11 cc 1 2 2015-02-14 14:20:00
12 cc 2 3 2015-02-14 14:25:00
我可以使用以下查询：
;with NewStatus2 as (
SELECT Id, IssueId, Updated, ROW_NUMBER() over (Order BY IssueId, id) PrevID FROM IssueStatus [is]
WHERE NewStatus = 2 )
, OldStatus2 as (
SELECT Id, IssueId, Updated, ROW_NUMBER() over (Order BY IssueId, id) PrevID FROM IssueStatus [is]
WHERE OldStatus = 2 )
SELECT ns.IssueId
, ns.Updated FromTime
, os.Updated ToTime
, DATEDIFF(minute , ns.Updated , os.Updated) as TimeSpan_Spent_In_Status_2
FROM NewStatus2 ns
INNER JOIN OldStatus2 os ON ns.IssueId = os.IssueId
AND ns.PrevID = os.PrevID
ORDER BY ns.Updated , os.Updated;
得到这个结果：
IssueId FromTime ToTime TimeSpan_Spent_In_Status_2
aa 2015-01-01 16:00:00 2015-01-01 17:30:00 90
bb 2015-02-13 12:56:00 2015-02-13 14:20:00 84
cc 2015-02-14 12:56:00 2015-02-14 13:19:00 23
cc 2015-02-14 14:20:00 2015-02-14 14:25:00 5
编辑由@vibhavSarraf提供的查询和示例数据匹配结构
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句