拆分 SQL 中的重叠时间

debugcn 发表于 Dev

阿拉文斯

我需要平均分割日期时间重叠的记录的持续时间。

例子：例如参考这张图片

根据示例，如果我计算订单 1 机器的总运行时间，则为 3 小时。但我希望它是 2 小时，因为在同一台机器上，另一个订单在这段时间（从上午 9 点到上午 11 点）之间运行。

我尝试搜索表单，所有内容都指向排除重叠持续时间或执行其他一些功能。但我想分割所有记录的重叠持续时间。

示例表结构：

declare @st datetime, @et datetime;

DECLARE @table TABLE (Machine varchar(4),OrderId varchar(6),StartTime DateTime2, EndTime DateTime2)

INSERT INTO @table SELECT 'M2','ORD1','2017-11-01 10:30:00.000', '2017-11-01 12:00:00.000'
INSERT INTO @table SELECT 'M2','ORD2','2017-11-01 11:00:00.000', '2017-11-01 12:30:00.000'
INSERT INTO @table SELECT 'M2','ORD3','2017-11-01 11:30:00.000', '2017-11-01 13:00:00.000'

预期结果：预期结果

基于上图，
ORD1 的持续时间 = 30 MIN + 15 MIN（ORD1 和 ORD2 之间的 30 MIN 重叠）+ 10 MIN（ORD1、ORD2 和 ORD3 之间的 30 MIN 重叠） ORD2 的
持续时间 = 15 MIN + 10 MIN + 15 MIN
ORD3 的持续时间 = 10 MIN + 15 MIN + 30 MIN

机器总运行时间将为 55 + 40 + 55 = 150 分钟（2 小时 30 分钟）

谢谢，阿拉文斯

阿拉文斯

感谢所有的回应。最后，在我们的一名团队成员的帮助下，这个场景已经完成。下面是解决方案，

 DECLARE @table TABLE (OrdId varchar(12),MId varchar(4), ST DateTime, ET DateTime)

INSERT INTO @table SELECT '10001','M1','2017-11-01 10:30:00.000', '2017-11-01 12:00:00.000' INSERT INTO @table SELECT '10002','M1','2017-11-01 11:00:00.000', '2017-11-01 12:30:00.000' INSERT INTO @table SELECT '10003','M1','2017-11-01 11:30:00.000', '2017-11-01 14:00:00.000'  INSERT INTO @table SELECT '10004','M2','2017-11-01 14:30:00.000', '2017-11-01 16:00:00.000' 

DECLARE @ST datetime, @ET datetime, @NEXT_ST datetime, @RC smallint, @MCHr smallint; set @MCHr = 0; set @ST = (select MIN(ST) AS ST from @table where  MId = 'M1' and OrdId = '10001') set @ET = (select MAX(ET) AS ET from @table where  MId = 'M1' and OrdId = '10001') WHILE @ST < @ET  BEGIN
       set @NEXT_ST = (select MIN(ST) AS ST from @table where  MId = 'M1' and ST > @ST)
       if @NEXT_ST is not null
              begin
                     set @RC = ( SELECT count(*) from @table where MId = 'M1' and (@ST >= ST and @ST < @NEXT_ST))
                     if @RC > 0
                     begin
                           SET @MCHr  = @MCHr + (select DATEDIFF(MI,0,@NEXT_ST-@ST) / @RC);
                     end;
                     set @ST = @NEXT_ST;
              end;
       else
              begin
                     set @NEXT_ST = (select MIN(ET) AS ET from @table where  MId = 'M1' and (@ST >= ST and @ST < ET))
                     set @RC = ( SELECT count(*) from @table where MId = 'M1' and (@ST >= ST and @ST < ET))
                     if @RC > 0
                           SET @MCHr  = @MCHr + (select DATEDIFF(MI,0,@NEXT_ST-@ST) / @RC)
                     set @ST = @NEXT_ST;
              end; END; select @MCHr as MCHr

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。