基于另一个表将一个记录拆分为多个

Question

我有一张桌子追踪位置。有一个ID、startdatetime、enddatetime和其他字段。

我有另一个表，其中的事件发生在每一个停留，具有相似的开始和结束时间，并链接到ID字段。

我需要做的是合并这两者，并将location表拆分成它的各个事件。这里的诀窍是，地点可能从2017-08-02开始，但第一次活动可能要几天后才会开始。因此，我需要一个记录的差距，在开始。

样本数据

 CREATE TABLE #Stays (
 EpID INT, StayId INT, StayStartDate DateTime, StayEndDate DateTime);
CREATE TABLE #Events (
 EpID INT, EventId INT, EventStartDate DateTime, EventEndDate DateTime, EventNumber INT);

INSERT INTO #Events SELECT   1, 7897, '2016-11-24 00:00:00.000','2016-11-26 00:00:00.000', 1
INSERT INTO #Events SELECT   1, 7898, '2016-11-26 00:00:00.000','2016-11-28 00:00:00.000', 2
INSERT INTO #Stays  SELECT   1, 10,   '2016-11-22 08:15:00.000','2016-11-24 10:54:00.000' 
INSERT INTO #Stays  SELECT   1, 11,   '2016-11-24 10:54:00.000','2016-11-24 11:17:00.000' 
INSERT INTO #Stays  SELECT   1, 12,   '2016-11-24 11:17:00.000','2016-11-25 08:16:00.000' 
INSERT INTO #Stays  SELECT   1, 13,   '2016-11-25 08:16:00.000','2016-11-28 23:15:00.000' 

预期产出将是

EpId    StartDate                 EndDate          EventNumber
1   2016-11-22 08:15:00.000 2016-11-23 23:59:59.000 NULL
1   2016-11-24 00:00:00.000 2016-11-25 23:59:59.000 7897
1   2016-11-26 00:00:00.000 2016-11-27 23:59:59.000 7898
1   2016-11-28 00:00:00.000 2016-11-28 23:15:00.000 NULL

这就是我在尝试的。它目前不能正常工作，而且我确信我正在研究的方法可能不是最好的。目前还没有将这两个数据集融合在一起。我的猜测是，有一个更容易的方法，通过外部或交叉应用，但我的知识，他们如何工作是相当有限的。有什么帮助吗？

;with e as (
SELECT [EpID]
      ,EventId
      ,[EventNumber]
      ,case when [EventStartDate] > DayStart then [EventStartDate] else DayStart end as [EventStart]
      ,case when [EventEndDate] < DayEnd then [EventEndDate] else DayEnd end as [EventEnd]
  FROM [Events] e
  inner join DimStaySegmentDayReference d on d.DayEnd >= e.[EventStartDate] and d.DayStart <= e.[EventEndDate]
),
s as (
  select 
    [EpID]
    ,StayId
     ,case when StayStartDate > DayStart then StayStartDate else DayStart end as [StayStart]
      ,case when StayEndDate < DayEnd then StayEndDate else DayEnd end as [StayEnd]

  from  Stays s
  inner join  DimStaySegmentDayReference d on d.DayEnd >= StayStartDate and d.DayStart <= StayEndDate
),

  u as (select 'stay' as source, [EpID],  StayStart, StayEnd, '' as event from s 
union all
select 'event' as source, [EpID],  [EventStart], [EventEnd], eventnumber as event  from e)

select  Source, 
    [EpID], 
    Staystart,
    stayend, 
    case when lag(stayend) over (partition by EpId ORDER BY STAYSTART) < StayEnd-0.0001 AND source='event' then lag(stayend) over (partition by EpId ORDER BY STAYSTART) else staystart end as staystartnew,
    case when lead(staystart) over (partition by EpID ORDER BY StayStart) < stayend then lead(staystart) over (partition by EpID ORDER BY StayStart) else stayend end as stayendnew,

    event
    from u 
    where StayStart <> stayend
order by StayStart

DayReference表只是每天都有一个开始和结束时间，这样我就可以将记录分割成日段。

我正在使用Server 2012

编辑一些上下文，我更新了我的示例数据，使其更加清晰。留宿桌追踪位置。在这种情况下，我忽略了多个位置，以便更容易地找到解决方案。位置和事件彼此不可知，而不是在相同的时间范围内发生相同的EpID。

例如，考虑跟踪工作时间，您从上午9点开始，下午5点结束。对于这一天的工作，你将有5个地点停留，以弥补全部的轮班。9-11次会议，11-12次会议，12-1次午餐，1-3次会议，3-5次会议。然后你会有一系列的事件，让我们称之为喝咖啡。你在9:30到10点之间喝咖啡，2-4点。

我需要做的是把这两组数据网格起来，创建一个单一的时间线。9-930桌，930-10杯咖啡，10-11桌，11-12次会议，12-1次午餐，1-2次会议，2-4杯咖啡，4-5桌。希望这能有所帮助

Answer 1

也许有些东西可以简化，但是很容易读懂我正在验证的每一种情况，而且，我认为输出示例中缺少一行，我得到了2018-09-14 16:00到2018-09-15 12:00的最后一行，我没有找到逻辑上的理由，也没有找到放弃它的问题。

需要额外的验证，并在没有注册事件的情况下左加入到停留，但这是我的方法。

;WITH CTE AS (
    SELECT D.*, s.StayId, 
    EventNumber,
    LAG(D.DStart) OVER (ORDER BY EventNumber) As LagStart,
    LAG(StayID) OVER (ORDER BY EventNumber) As LagStay,
    LAG(Event) OVER (ORDER BY EventNumber) As LagEvent,
    LEAD(D.DEnd) OVER (ORDER BY EventNumber) As LeadEnd,
    LEAD(StayID) OVER (ORDER BY EventNumber) As LeadStay,
    LEAD(Event) OVER (ORDER BY EventNumber) As LeadEvent
    FROM #Events E
    CROSS APPLY
    (
        SELECT TOP 1 * FROM #Stays S WHERE E.EventStartDate BETWEEN S.StayStartDate AND S.StayEndDate
        UNION
        SELECT TOP 1 * FROM #Stays S WHERE E.EventEndDate BETWEEN S.StayStartDate AND S.StayEndDate
    ) S
    CROSS APPLY (
        SELECT StayStartDate AS DStart, EventStartDate DEnd, Null AS Event, 1 as c WHERE StayStartDate < EventStartDate
        UNION
        SELECT EventStartDate, EventEndDate, EventNumber, 2 WHERE EventStartDate >= StayStartDate AND EventEndDate <= StayEndDate
        UNION
        SELECT StayStartDate, EventEndDate, EventNumber, 3 WHERE StayStartDate > EventStartDate AND EventEndDate < StayEndDate
        UNION
        SELECT EventStartDate, StayEndDate, EventNumber, 4 WHERE StayStartDate < EventStartDate AND EventEndDate > StayEndDate
        UNION
        SELECT EventEndDate, StayEndDate, Null, 5 WHERE EventEndDate < StayEndDate
    ) D
)
SELECT DISTINCT 
CASE WHEN LagStay = StayId AND Event IS NULL AND LagEvent IS NULL THEN LagStart 
ELSE DStart END AS StartDate,
CASE WHEN LeadStay = StayId AND Event IS NULL AND LeadEvent IS NULL THEN LeadEnd 
ELSE DEnd END AS EndDate,
Event, StayID
FROM CTE
ORDER BY StartDate