从日期范围组中查找重叠（公共）日期范围

遵循以下几行的内容应该可以满足您的需要（DEMO）

WITH DistinctCounts
     AS (SELECT EventCount = COUNT(DISTINCT EID),
                PID
         FROM   #EventsTBL
         GROUP  BY PID),
     T1([PID], EID, Date, Flag)
     AS (SELECT [PID],
                EID,
                StartDate,
                1
         FROM   #EventsTBL
         UNION ALL
         SELECT [PID],
                EID,
                EndDate,
                -1
         FROM   #EventsTBL),
     T2
     AS (SELECT *,
                ActiveCount = SUM(Flag) OVER (PARTITION BY [PID] ORDER BY Date ROWS UNBOUNDED PRECEDING)
         FROM   T1),
     T3
     AS (SELECT *,
                PrevActiveCount = LAG(ActiveCount, 1, -1) OVER (PARTITION BY [PID] ORDER BY Date)
         FROM   T2),
     T4
     AS (SELECT T3.*,
                RN = ROW_NUMBER() OVER (PARTITION BY T3.[PID] ORDER BY T3.Date) - 1
         FROM   T3
         WHERE  ActiveCount <> PrevActiveCount
                AND EXISTS (SELECT *
                            FROM   DistinctCounts DC
                            WHERE  DC.PID = T3.PID
                                   AND DC.EventCount IN ( T3.ActiveCount, T3.PrevActiveCount )))
SELECT PID,
       StartDate = MIN(Date),
       EndDate = MAX(Date)
FROM   T4
GROUP  BY PID,
          RN / 2

我提出了一个 T-SQL 的解决方案。

小提琴 SQL Server 2017

小提琴 SQL Server 2017 ALT#1

问题：

按程序查找所有可用事件的会话都处于活动状态的日期范围交叉点。

解决方案：

下面我分三个步骤解释如何确定交叉点，交叉点的并发性和程序的最大并发性。

第 1 步：查询一组连续的日期间隔，这些日期间隔由会话开始或停止的程序分区。

with SessionIntersections (PID, StartDate, EndDate) as (
    select distinct s.PID, s.StartDate, min(e.EndDate) EndDate from (
        select PID, StartDate from EventsTBL
        union 
        select PID, EndDate from EventsTBL
    ) s
    inner join (
        select PID, EndDate from EventsTBL
        union 
        select PID, StartDate from EventsTBL
    ) e on e.EndDate > s.StartDate 
        and s.PID = e.PID
    where s.StartDate is not null 
    and e.EndDate is not null
    group by s.PID, s.StartDate
)

步骤2：查询并发（程序同时运行的不同事件数）等于最大并发（程序可用的不同事件总数）的相关日期范围。

ConcurrentSessions (PID, StartDate, EndDate, concurrency, maxconcurrency) as (
    select mcs.PID, StartDate, EndDate, concurrency, maxconcurrency from (
        select i.PID, i.StartDate, i.EndDate, count(distinct(t.EID)) concurrency, min(maxconcurrency) maxconcurrency
        from SessionIntersections i
        inner join EventsTBL t 
            on t.StartDate < i.EndDate 
            and i.StartDate < t.EndDate
            and i.PID = t.PID
            inner join (
                select q.PID, count(distinct(q.EID)) maxconcurrency
                from EventsTBL q
                group by q.PID
            ) mc on mc.PID = i.PID
        group by i.PID, i.StartDate, i.EndDate
    ) mcs
)

第三步：输出达到最大并发的日期范围

from ConcurrentSessions
where concurrency = maxconcurrency
order by PID, StartDate;

完整查询：

with SessionIntersections (PID, StartDate, EndDate) as (
    select distinct s.PID, s.StartDate, min(e.EndDate) EndDate from (
        select PID, StartDate from EventsTBL
        union 
        select PID, EndDate from EventsTBL
    ) s
    inner join (
        select PID, EndDate from EventsTBL
        union 
        select PID, StartDate from EventsTBL
    ) e on e.EndDate > s.StartDate 
        and s.PID = e.PID
    where s.StartDate is not null 
    and e.EndDate is not null
    group by s.PID, s.StartDate
)
, ConcurrentSessions (PID, StartDate, EndDate, concurrency, maxconcurrency) as (
    select mcs.PID, StartDate, EndDate, concurrency, maxconcurrency from (
        select i.PID, i.StartDate, i.EndDate, count(distinct(t.EID)) concurrency, min(maxconcurrency) maxconcurrency
        from SessionIntersections i
        inner join EventsTBL t 
            on t.StartDate < i.EndDate 
            and i.StartDate < t.EndDate
            and i.PID = t.PID
            inner join (
                select q.PID, count(distinct(q.EID)) maxconcurrency
                from EventsTBL q
                group by q.PID
            ) mc on mc.PID = i.PID
        group by i.PID, i.StartDate, i.EndDate
    ) mcs
)
select PID, StartDate, EndDate 
from ConcurrentSessions
where concurrency = maxconcurrency
order by PID, StartDate;