需要检查的日期在SQL Server重叠或不

Question

下面是我输入代码

ID EMP_ID  PROJECT_NAME   START_DATE   END_DATE 
1 10016351 ABC      22-12-2016  15-05-2017 
2 10016351 ABC      01-09-2016  22-11-2016 
1 10081503 RTBS AMaaS    21-11-2016  15-02-2017 
2 10081503 RTBS AMaaS    18-07-2016  25-11-2016 
3 10081503 RTBS AMaaS    21-08-2016  13-10-2016 
4 10081503 RTBS AMaaS    03-02-2015  22-05-2015 
1 10089293 RTBS PDaaS    17-02-2017  31-12-2017 
2 10089293 RTBS PDaaS    13-06-2016  14-02-2017 

我需要检查的雇员，日期重叠或没有。如果是，那么它将返回最小开始日期和最大结束日期并标记为'O'。

例如，对于emp_id = 10081503日期重叠 21-11-2016 is 18-07-2016 and 25-11-2016之间，也 21-08-2016 is 18-08-2016之间和25-11-2016 因此查询应该会将重叠日期的开始日期返回为18-07-2016（分钟）并将结束日期返回为 15-02-2017（最大）。 对于其余条目，它应该返回与标志'N'相同的条目。

EMP_ID | PROJECT_NAME | START_DATE | END_DATE |国旗 10081503 | RTBS AMaaS | 21-11-2016 | 15-02-2017 | O 10081503 | RTBS AMaaS | 03-02-2015 | 22-05-2015 | ñ

像明智我最后的输出应该是，

ID EMP_ID  PROJECT_NAME   START_DATE   END_DATE FLAG 
1 10016351 ABC      22-12-2016  15-05-2017 N 
2 10016351 ABC      01-09-2016  22-11-2016 N 
1 10081503 RTBS AMaaS    21-11-2016  15-02-2017 O 
2 10081503 RTBS AMaaS    03-02-2015  22-05-2015 N 
1 10089293 RTBS PDaaS    17-02-2017  31-12-2017 N 
2 10089293 RTBS PDaaS    13-06-2016  14-02-2017 N 

Answer 1

使用outer apply()返回第一id由该值重叠和分组。使用row_number()重新编号id：

select 
    id = row_number() over (
     partition by t.emp_id 
     order by min(start_date) desc 
    ) 
    , t.emp_id 
    , t.project_name 
    , start_date = min(start_date) 
    , end_date = max(end_date) 
    , flag = max(case when x.id <> t.id then 'O' else 'N' end) 
from t 
    outer apply (
    select top 1 i.id 
    from t i 
    where i.Emp_Id = t.Emp_id 
     and i.End_Date > t.Start_Date 
     and t.End_Date > i.Start_Date 
    order by i.Start_Date 
    ) x 
group by t.emp_id, t.project_name, x.id 

rextester演示：http://rextester.com/VHMZ91714

回报：

+----+----------+--------------+------------+------------+------+ 
| id | emp_id | project_name | start_date | end_date | flag | 
+----+----------+--------------+------------+------------+------+ 
| 1 | 10016351 | ABC   | 2016-12-22 | 2017-05-15 | N | 
| 2 | 10016351 | ABC   | 2016-09-01 | 2016-11-22 | N | 
| 1 | 10081503 | RTBS AMaaS | 2016-07-18 | 2017-02-15 | O | 
| 2 | 10081503 | RTBS AMaaS | 2015-02-03 | 2015-05-22 | N | 
| 1 | 10089293 | RTBS PDaaS | 2017-02-17 | 2017-12-31 | N | 
| 2 | 10089293 | RTBS PDaaS | 2016-06-13 | 2017-02-14 | N | 
+----+----------+--------------+------------+------------+------+ 

---

以上会崩溃互相重叠的范围，但如果你需要崩溃多个范围只在边缘重叠成一个范围，我们可以使用日历或日期表，如下所示：

对于内存只有152KB，可以有30个年份的日期的表中的这个：

/* dates table */ 
declare @fromdate date = '20000101'; 
declare @years int = 30; 
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */ 
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) 
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate))) 
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate)) 
into dbo.Dates 
from n as deka cross join n as hecto cross join n as kilo 
       cross join n as tenK cross join n as hundredK 
order by [Date]; 
create unique clustered index ix_dbo_Dates_date on dbo.Dates([Date]); 

如果不想创建一个日期表，您可以使用此查询生成日期，像这样：

declare @fromdate date = '20100101'; 
declare @thrudate date = getdate(); 
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) 
, dates as (
    select top (datediff(day, @fromdate, @thrudate)+1) 
     [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate)) 
    from n as deka cross join n as hecto cross join n as kilo 
       cross join n as tenK cross join n as hundredK 
    order by [Date] 
) 
, cte as (
    select 
     t.emp_id 
    , t.project_name 
    , d.date 
    , rn = row_number() over (partition by t.emp_id, t.project_name order by d.date)-1 
    , flag = case when count(*) > 1 then 'O' else 'N' end 
    from t 
    inner join dates d 
     on d.date >= t.start_date 
    and d.date <= t.end_date 
    group by t.emp_id, t.project_name, d.date 
) 
select 
    id = row_number() over (partition by emp_id order by min(date) desc) 
    , emp_id 
    , project_name 
    , start_date = min(date) 
    , end_date = max(date) 
    , flag = max(flag) 
from cte 
group by emp_id, project_name, dateadd(day,-rn,date) 

rextester演示：http://rextester.com/QKEMH32326

回报：

+----+----------+--------------+------------+------------+------+ 
| id | emp_id | project_name | start_date | end_date | flag | 
+----+----------+--------------+------------+------------+------+ 
| 1 | 10016351 | ABC   | 2016-12-22 | 2017-05-15 | N | 
| 2 | 10016351 | ABC   | 2016-09-01 | 2016-11-22 | N | 
| 1 | 10081503 | RTBS AMaaS | 2016-07-18 | 2017-02-15 | O | 
| 2 | 10081503 | RTBS AMaaS | 2015-02-03 | 2015-05-22 | N | 
| 1 | 10089293 | RTBS PDaaS | 2017-02-17 | 2017-12-31 | N | 
| 2 | 10089293 | RTBS PDaaS | 2016-06-13 | 2017-02-14 | N | 
+----+----------+--------------+------------+------------+------+ 

日历和数字表参考：

• Generate a set or sequence without loops 2- Aaron Bertrand
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
• TSQL Function to Determine Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones

Answer 2

谢谢大家的帮助。

需要一些帮助，在同样的情况下，

碰上了以下的情况下，

在这种情况下，我们将得到的分配明细表两个条目。这是因为资源分配给两个不同的项目，但第一个分配项目的分配结束日期（在上例中）与第二个分配项目的分配开始日期重叠。 （Alloc 1）= Alloc Start Date（Alloc 2） - Alloc Start Date（Alloc 1） 即（17-11-2016） - （01-09- 2016）= 77 分配2的任期计算没有变化。 这里，最后的条目将出现如下：