查找总持续时间 [英] Finding the total duration
本文介绍了查找总持续时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好，
我的表结构如下
Date_Time [590] [591] [592]
2013-03-27 11:00:00 125 7 1
2013-03-27 11:00:05 125 4 1
2013-03-27 11:00:10 125 7 1
2013-03-27 11:00:15 125 7 0
2013-03-27 11:00:20 155 7 0
2013-03-27 11:00:25 125 6 1
2013-03-27 11:00:30 185 7 1
2013-03-27 11:00:35 125 7 1
2013-03-27 11:00:40 125 2 0
2013-03-27 11:00:45 125 7 0
2013-03-27 11:00:50 155 7 1
2013-03-27 11:00:55 125 8 1
2013-03-27 11:01:00 125 7 1
在上表中，列[592]的值为0或1.当值为1时，表示进程正在进行中，当进程完成值[ 592]变为0.现在，我想计算过程延迟（即）t他介于两个过程之间的时间。喜欢我需要计算一天和一个月的过程延迟。
换句话说，我需要计算总持续时间[592]当它为0和一个月时。
[edit]已添加代码块 - OriginalGriff [/ edit]
解决方案
它''很难用简短的描述回答一个问题，但我会猜测......
MS SQL Server 2005及更高版本的解决方案。
- 声明临时表
< span class =code-keyword> DECLARE @ tbl TABLE （Date_Time DATETIME ，[ 590 ] INT ，[ 591 ] INT ，[ 592 ] INT ）
- 插入数据
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:54:00'， 1645 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:54:05'， 1645 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：54：10'， 1645 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:54:15'， 1645 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：54：20'， 1645 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:54:25'， 1645 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：54：30'， 1645 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:54:35'， 1645 ， 0 ， 0 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：54：40'， 1645 ， 0 ， 0 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:54:45'， 1645 ， 0 ， 0 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：54：50'， 1646 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:54:55'， 1646 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：55：00'， 1646 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:55:05'， 1646 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：55：10'， 1646 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:55:15'， 1646 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：55：20'， 1646 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:55:25'， 1646 ， 0 ， 0 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：55：30'， 1646 ， 0 ， 0 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:55:35'， 1646 ， 0 ， 0 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：55：40'， 1647 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:55:45'， 1647 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：55：50'， 1647 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:55:55'， 1647 ， 0 ， 1 ）
INSERT INTO @ tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16 ：56：00'， 1647 ， 0 ， 1 ）
INSERT INTO @tbl （Date_Time，[ 590 ]，[ 591 ]，[ 592 ]）
VALUES （' 2013-03-28 16:56:05'， 1647 ， 0 ， 1 ）
- 声明另一个临时表
DECLARE @ tmp TABLE （Process INT ，StartDate DATETIME ，EndDate DATETIME ，DurationInSeconds INT ）
- 插入数据
INSERT INTO @ tmp （Process，StartDate，EndDate，DurationInSeconds）
SELECT T. *，DATEDIFF（ss，[StartDate]，[EndDa te]） AS DurationInSeconds
FROM （
SELECT t1。[ 590 ] AS 处理，MIN（t1.Date_Time） AS StartDate，t2.EndDate
FROM @ tbl AS t1 LEFT JOIN （
SELECT [ 590 ] AS Process，MIN（Date_Time） AS EndDate
FROM @ tbl
WHERE [ 592 ] = 0
GROUP BY [ 590 ]
） AS t2 ON t1。[ 590 ] = t2.Process
WHERE t1。[ 592 ] = 1
GROUP BY t1。[ 590 ]，t2.EndDate
） AS T
ORDER BY T.Process
- 查看结果：流程开始时间，结束时间和剩余流程时间
SELECT *
< span class =code-keyword> FROM @ tmp
- 执行2个公用表表达式
; WITH cte_EndOfProcess AS
（
- 获取进程数及其结束日期; RowNo - >临时密钥
SELECT ROW_NUMBER（） OVER （ ORDER BY 处理） AS RowNo，Process，EndDate As ProcessEndsAt
FROM @ tmp
），
cte_StartNextProcess AS
（
- 获取下一个进程数及其开始日期;设置RowNo-1以创建此行与上一行数据之间的关系
SELECT ROW_NUMBER（） OVER （ ORDER BY 进程）-1 AS RowNo，Process，StartDate As NextProcessStartsAt
FROM @ tmp
）
SELECT f.RowNo，f.Process，f.ProcessEndsAt，n.Process AS NextProcess，n.NextProcessStartsAt，DATEDIFF（ss，f。 ProcessEndsAt，n.NextProcessStartsAt） AS TimeDiffBetweenProc
FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo
第一个SELECT语句的结果：
处理StartDate EndDate TimeDiff ...
1645 2013-03-28 16：54：00.000 2013-03-28 16 ：54：35.000 35
1646 2013-03-28 16：54：50.000 2013-03-28 16：55：25.000 35
1647 2013-03-28 16：55：40.000 NULL NULL
第二个SELECT语句（CTE'）的结果
RowNo Process ProcessEndsAt NextP .. NextProcessStartsAt TimeDiff。 ..
1 1645 2013-03-28 16：54：35.000 1646 2013-03-28 16：54：50.000 15
2 1646 2013-03-28 16：55：25.000 1647 2013-03 -28 16：55：40.000 15
3 1647 NULL NULL NULL NULL
来计算总数，使用：
SELECT COUNT（f.RowNo）CountOfProcesses，SUM（DATEDIFF（ss，f.ProcessEndsAt，n.NextProcessStartsAt） ） AS TotalTime
FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo
WHERE COALESCE （DATEDIFF（ss，f。 ProcessEndsAt，n.NextProcessStartsAt）， 0 ）> 0
而不是
SELECT f.RowNo，f.Process，f.ProcessEndsAt，n.Process AS NextProcess，n.NextProcessStartsAt，DATEDIFF（ss，f.ProcessEndsAt，n.NextProcessStartsAt） AS TimeDiffBetweenProc
FROM cte_EndOfProcess AS f LEFT 加入 cte_StartNextProcess AS n ON f.RowNo = n.RowNo
结果：
CountOfProcesses TotalTime
2 30
Hi All,
I have a table structure as follows
Date_Time [590] [591] [592]
2013-03-27 11:00:00 125 7 1
2013-03-27 11:00:05 125 4 1
2013-03-27 11:00:10 125 7 1
2013-03-27 11:00:15 125 7 0
2013-03-27 11:00:20 155 7 0
2013-03-27 11:00:25 125 6 1
2013-03-27 11:00:30 185 7 1
2013-03-27 11:00:35 125 7 1
2013-03-27 11:00:40 125 2 0
2013-03-27 11:00:45 125 7 0
2013-03-27 11:00:50 155 7 1
2013-03-27 11:00:55 125 8 1
2013-03-27 11:01:00 125 7 1
In the above table,column [592] have the values either 0 or 1.when the value is 1 it means process was in progress and when the process completes the value of [592] changes to 0.Now, I want to calculate the process delay (ie) the time between taken between two process.Likewise i need to calculate the process delay for a day as well as for month.
In other words,I need to calculate the total duration of [592] when it was 0 for a day as well as for a month.
[edit]Code block added - OriginalGriff[/edit]
解决方案
It''s hard to answer a question with short description, but i''ll try to guess...
Solution for MS SQL Server 2005 and higher.
--declare temporary table DECLARE @tbl TABLE (Date_Time DATETIME, [590] INT, [591] INT, [592] INT) --insert data INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:00', 1645, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:05', 1645, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:10', 1645, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:15', 1645, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:20', 1645, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:25', 1645, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:30', 1645, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:35', 1645, 0, 0) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:40', 1645, 0, 0) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:45', 1645, 0, 0) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:50', 1646, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:54:55', 1646, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:00', 1646, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:05', 1646, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:10', 1646, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:15', 1646, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:20', 1646, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:25', 1646, 0, 0) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:30', 1646, 0, 0) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:35', 1646, 0, 0) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:40', 1647, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:45', 1647, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:50', 1647, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:55:55', 1647, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:56:00', 1647, 0, 1) INSERT INTO @tbl (Date_Time, [590], [591], [592]) VALUES ('2013-03-28 16:56:05', 1647, 0, 1) --declare another temporary table DECLARE @tmp TABLE(Process INT, StartDate DATETIME, EndDate DATETIME, DurationInSeconds INT) --insert data INSERT INTO @tmp (Process, StartDate, EndDate, DurationInSeconds) SELECT T.*, DATEDIFF(ss,[StartDate],[EndDate]) AS DurationInSeconds FROM ( SELECT t1.[590] AS Process, MIN(t1.Date_Time) AS StartDate, t2.EndDate FROM @tbl AS t1 LEFT JOIN ( SELECT [590] AS Process, MIN(Date_Time) AS EndDate FROM @tbl WHERE [592]=0 GROUP BY [590] ) AS t2 ON t1.[590] = t2.Process WHERE t1.[592]=1 GROUP BY t1.[590], t2.EndDate ) AS T ORDER BY T.Process --view result: process start time, end time and remaining time of process SELECT * FROM @tmp --execute 2 common table expression ;WITH cte_EndOfProcess AS ( --get number of process and its ending date; RowNo -> temporary key SELECT ROW_NUMBER() OVER (ORDER BY Process) AS RowNo, Process, EndDate As ProcessEndsAt FROM @tmp ), cte_StartNextProcess AS ( --get next number of process and its starting date; set RowNo-1 to create relation between this row and previous row of data SELECT ROW_NUMBER() OVER (ORDER BY Process) -1 AS RowNo, Process, StartDate As NextProcessStartsAt FROM @tmp ) SELECT f.RowNo, f.Process, f.ProcessEndsAt, n.Process AS NextProcess, n.NextProcessStartsAt, DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt) AS TimeDiffBetweenProc FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo
result of first SELECT statement:
Process StartDate EndDate TimeDiff... 1645 2013-03-28 16:54:00.000 2013-03-28 16:54:35.000 35 1646 2013-03-28 16:54:50.000 2013-03-28 16:55:25.000 35 1647 2013-03-28 16:55:40.000 NULL NULL
result of second SELECT statement (CTE''s)
RowNo Process ProcessEndsAt NextP.. NextProcessStartsAt TimeDiff... 1 1645 2013-03-28 16:54:35.000 1646 2013-03-28 16:54:50.000 15 2 1646 2013-03-28 16:55:25.000 1647 2013-03-28 16:55:40.000 15 3 1647 NULL NULL NULL NULL
to count total, use:
SELECT COUNT(f.RowNo) CountOfProcesses, SUM(DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt)) AS TotalTime FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo WHERE COALESCE(DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt),0)>0
instead of
SELECT f.RowNo, f.Process, f.ProcessEndsAt, n.Process AS NextProcess, n.NextProcessStartsAt, DATEDIFF(ss, f.ProcessEndsAt, n.NextProcessStartsAt) AS TimeDiffBetweenProc FROM cte_EndOfProcess AS f LEFT JOIN cte_StartNextProcess AS n ON f.RowNo = n.RowNo
result:
CountOfProcesses TotalTime 2 30
这篇关于查找总持续时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
