TSQL从datetime范围获取重叠的时间段 [英] TSQL get overlapping periods from datetime ranges

问题描述

这是一个模式示例：

我有一个日期范围的表，我需要它的行之间的重叠周期（以小时为单位）的总和。 >

 创建表格周期（
 id int，
 starttime datetime，
 endtime datetime，
类型varchar（64）
）; 
 
插入到期间值（1，'2013-04-07 8:00'，'2013-04-07 13:00'，'工作'）; 
插入到期间值（2，'2013-04-07 14:00'，'2013-04-07 17:00'，'工作'）; 
插入期间值（3，'2013-04-08 8:00'，'2013-04-08 13:00'，'工作'）; 
插入到期间值（4，'2013-04-08 14:00'，'2013-04-08 17:00'，'工作'）; 
插入到期间值（5，'2013-04-07 10:00'，'2013-04-07 11:00'，'Holyday'）; / * 1h与1 * / 
重叠插入到期间值（6，'2013-04-08 10:00'，'2013-04-08 20:00'，'转移'）; / * 6h与3和4 * / 
重叠插入到期间值（7，'2013-04-08 11:00'，'2013-04-08 12:00'，'测试'）; / * 1h与3和6重叠* / 
  

它的小提琴： http://sqlfiddle.com/#!6/9ca31/10

$ b $我期望一个8小时重叠的小时数：
1h（id 5 over id 1）
6h（id 6 over id 3 and 4）
1h（id 7 over id 3和6）

我检查：选择与SQL 重叠的datetime事件，但似乎不能做我需要的。

谢谢。

解决方案

  select sum（datediff（hh，case when t2.starttime> t1.starttime then t2.starttime else t1.starttime end， 
 case when t2.endtime> t1.endtime then t1.endtime else t2.endtime end））
 from period t1 
 join period t2 on t1.id< t2.id 
其中t2.endtime> t1.starttime和t2.starttime< t1.endtime; 
  

更新以处理几个重叠：

  select sum（datediff（hh，start，fin））
 from（select distinct 
 case when t2.starttime> t1.starttime then t2.starttime else t1。开始时间结束为开始，
情况下t2.endtime> t1.endtime then t1.endtime else t2.endtime end as fin 
 from period t1 
 join period t2 on t1.id< t2.id 
其中t2.endtime> t1.starttime和t2.starttime< t1.endtime 
）作为重叠; 
  

I have a table with date range an i need the sum of overlapping periods (in hours) between its rows.

This is a schema example:

create table period (
    id int,
    starttime datetime,
    endtime datetime,
    type varchar(64)
  );

insert into period values (1,'2013-04-07 8:00','2013-04-07 13:00','Work');
insert into period values (2,'2013-04-07 14:00','2013-04-07 17:00','Work');
insert into period values (3,'2013-04-08 8:00','2013-04-08 13:00','Work');
insert into period values (4,'2013-04-08 14:00','2013-04-08 17:00','Work');
insert into period values (5,'2013-04-07 10:00','2013-04-07 11:00','Holyday'); /* 1h overlapping with 1*/
insert into period values (6,'2013-04-08 10:00','2013-04-08 20:00','Transfer'); /* 6h overlapping with 3 and 4*/
insert into period values (7,'2013-04-08 11:00','2013-04-08 12:00','Test');  /* 1h overlapping with 3 and 6*/

And its fiddle: http://sqlfiddle.com/#!6/9ca31/10

I expect a sum of 8h overlapping hours: 1h (id 5 over id 1) 6h (id 6 over id 3 and 4) 1h (id 7 over id 3 and 6)

I check this: select overlapping datetime events with SQL but seems to not do what I need.

Thank you.

解决方案

select sum(datediff(hh, case when t2.starttime > t1.starttime then t2.starttime else t1.starttime end,
    case when t2.endtime > t1.endtime then t1.endtime else t2.endtime end))
from period t1 
join period t2 on t1.id < t2.id
where t2.endtime > t1.starttime and t2.starttime < t1.endtime;

Updated to handle several overlaps:

select sum(datediff(hh, start, fin))
from (select distinct
case when t2.starttime > t1.starttime then t2.starttime else t1.starttime end as start,
case when t2.endtime > t1.endtime then t1.endtime else t2.endtime end as fin
from period t1 
join period t2 on t1.id < t2.id
where t2.endtime > t1.starttime and t2.starttime < t1.endtime
) as overlaps;

这篇关于TSQL从datetime范围获取重叠的时间段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！