许多停止和启动设备的许多日期之间的日期差(工作时间)

Question

我有一个这样的表：

date        | machine | activity_type | Status
31.12.2018 23:15:00 | device2 |     type2     | Start
1.1.2019   00:05:00 | device1 |     type1     | Start
1.1.2019   00:10:00 | device2 |     type2     |  Stop
1.1.2019   00:15:00 | device2 |     type1     | Start
1.1.2019   00:20:00 | device1 |     type1     |  Stop
1.1.2019   00:25:00 | device1 |     type3     | Start
2.1.2019   00:23:00 | device1 |     type3     |  Stop
3.1.2019   00:00:00 | device2 |     type1     |  Stop

我想要的输出(2019年1月1日(是：

date              | Duration | Activity_type | Machine
1.1.2019 00:05:00 | 00:15:00 |    type1      | device1
1.1.2019 00:00:00 | 00:10:00 |    type2      | device2
1.1.2019 00:15:00 | 23:45:00 |    type1      | device2
1.1.2019 00:25:00 | 23:35:00 |    type3      | device1

因此：

Device1在00:05:00开始，在00:20:00停止，因此持续时间为00:15:00。

Device2在00:10:00停止(它的工作在前一天开始(，所以持续时间是00:10:00。

Device2从00:15:00开始，当天不会停止工作，因此持续时间为23:45:00。

Device1从00:25:00开始，当天也不会停止工作，因此持续时间为23:35:00

我想出了这个，但它一直显示空

SELECT
w1.Machine,
w1.Date,
w1.Type,
DateDiff(SECOND,[prevdate],[Date]) AS Duration
FROM
(
SELECT
Maszyna,
Typ,
Date,
(
SELECT Max(Date)
FROM WorkHours as T2
WHERE
T2.Machine=T1.Machine
AND T2.Type=T1.Type
AND T2.Date < T1.Date
) AS prevdate
FROM WorkHours AS T1
) as w1;

Answer 1

这里我已经为您的案例完成了T-SQL。

架构：

CREATE TABLE Your_Table(date DATETIME,machine VARCHAR(100),activity_type VARCHAR(100), Status VARCHAR(10))
INSERT INTO Your_Table VALUES('2018-12-31 23:15:00','device2','type2','Start')
INSERT INTO Your_Table VALUES('2019-01-01 00:05:00','device1','type1','Start')
INSERT INTO Your_Table VALUES('2019-01-01 00:10:00','device2','type2','Stop')
INSERT INTO Your_Table VALUES('2019-01-01 00:15:00','device2','type1','Start')
INSERT INTO Your_Table VALUES('2019-01-01 00:20:00','device1','type1','Stop')
INSERT INTO Your_Table VALUES('2019-01-01 00:25:00','device1','type3','Start')
INSERT INTO Your_Table VALUES('2019-01-02 00:23:00','device1','type3','Stop')
INSERT INTO Your_Table VALUES('2019-01-03 00:00:00','device2','type1','Stop')
INSERT INTO Your_Table VALUES('2019-01-03 00:05:00','device2','type2','Start')
INSERT INTO Your_Table VALUES('2019-01-03 00:25:00','device2','type2','Stop')

我在变量中传递date value，并在查询中使用它们。

DECLARE @Date DATETIME = '2019-01-01 00:00:00',@EndDate DATETIME = '2019-01-02 00:00:00'
SELECT MIN(date),
CONVERT(varchar(12),DATEADD(minute,DATEDIFF(MINUTE,CASE WHEN MAX(CASE WHEN Status = 'Start' THEN date END)<@Date THEN @Date ELSE MAX(CASE WHEN Status = 'Start' THEN date END) END,
CASE WHEN MAX(CASE WHEN Status = 'Stop' THEN date END)>@EndDate THEN @EndDate ELSE MAX(CASE WHEN Status = 'Stop' THEN date END) END),0), 114)
,Activity_type
,Machine
FROM(
SELECT *,
((ROW_NUMBER() OVER(PARTITION BY machine,activity_type ORDER BY date))+1)/2 RN 
FROM Your_Table
)A
GROUP BY Activity_type,Machine,RN

您可以在SQL Fiddle 中检查输出

Answer 2

这里有一个解决方案，您可以将表连接到表本身，并使用窗口函数为每个启动事件返回下一个相应的停止事件

查询返回1月1日开始的所有事件及其各自的停止事件(停止事件具有相同的Machine和Activity Type(

注意-我认为预期输出中的第二行是错误的，因为设备2，类型2没有停止事件(看起来也有拼写错误-1.1.2019 00:00:00应该是1.1.2019 00:10:00吗？(

CREATE TABLE StartTimes
(
[Date]          DATETIME,
Machine         VARCHAR(7),
Activity_Type   VARCHAR(5),
[Status]        VARCHAR(5)
)
INSERT INTO StartTimes VALUES
('12/31/2018 23:15:00','device2','type2','Start'),
('1/1/2019   00:05:00','device1','type1','Start'),
('1/1/2019   00:10:00','device2','type2','Stop'),
('1/1/2019   00:15:00','device2','type1','Start'),
('1/1/2019   00:20:00','device1','type1','Stop'),
('1/1/2019   00:25:00','device1','type3','Start'),
('1/2/2019   00:23:00','device1','type3','Stop'),
('1/3/2019   00:00:00','device2','type1','Stop')
SELECT  StartDate,
EndDate,
CONVERT(varchar(12), DATEADD(MINUTE,DATEDIFF(MINUTE,StartDate,EndDate),0),114) AS Duration,
Activity_Type,
Machine
FROM    (
SELECT    s.[Date] AS StartDate,
s.Machine,
s.Activity_Type,
s.[Status],
s1.[Date] AS EndDate,
ROW_NUMBER() OVER (PARTITION BY s.date ORDER BY s1.Date) as RowN
FROM      StartTimes s
JOIN StartTimes s1
ON s.[Date] < s1.[Date] AND
s.Machine = s1.Machine AND
s.Activity_Type = s1.Activity_Type AND
s.Status = 'Start' AND
s1.Status = 'Stop'
) a
WHERE  RowN= 1 AND
[StartDate] >= '2019-01-01' AND
[StartDate]  < '2019-01-02'
ORDER BY StartDate