T-SQL 脚本 - 时间线的逻辑问题 [英] T-SQL script - logic issues with timeline

问题描述

创建并加载两个临时表...这是架构.

Two temp tables are created and then loaded...Here's the schema.

Create table #SH ([date] datetime,
        sched_id int,
        agent_id int)

Create table #SD (sched_id int,
        start_minute int,
        length int,
        exception_code int)

(不幸的是，架构和设计是我无法更改的，两个临时表都是从平面文件加载的.如果需要，我可以引入和加载新的临时表).

(Schema and design is something I can't change unfortunately, both temp tables are loaded from flat files. I can introduce and load new temp tables if needed).

一点背景 - #SH 标题表将人员日程表保存为Start_minute"，并以分钟为单位显示schedule_length".例如，如果开始分钟和计划长度均为 480，则将读取为上午 8 点(上午 8 点 = 第 480 分钟)并持续到下午 4 点(480 分钟后，下午 4 点 = 第 960 分钟)

A little background - The #SH header table holds a persons schedule as 'Start_minute' and goes for 'schedule_length' in minutes. For example, if start minute and schedule length were both 480, that would read as 8am (8am = 480th minute) and goes until 4pm (480 minutes later, 4pm = 960th minute)

#SD 表包含标题的例外情况.在上面的示例中，此人可能会有一个午餐例外，即 start_minute = 720 和长度为 30 (12:00 - 12:30).

The #SD table holds exceptions to the header. In the example above, the person would likely have a lunch exception which would be start_minute = 720 and length of 30 (12:00 - 12:30).

日期和 agent_id 是我对#SH 唯一感兴趣的，#sd 中的异常信息是我感兴趣的.

Date and agent_id is the only thing I'm interested out of #SH, the exception info in #sd is what I'm interested in.

此查询有效:

Select [date],#sd.start_minute,#sd.length,#sd.start_minute + #sd.length as 'end_minute',agent_id
from #SH 
inner join #SD on #SD.sched_id = #sh.sched_id

*end_minute 最终是 start+length = end 的计算值

*end_minute is ultimately a calculated value of start+length = end

这将返回如下内容:

   Date     Start  length   end

1 2010-11-11 600    30  630

2 2010-11-11 630    40  670

3 2010-11-11 750    15  765

4 2010-11-11 800    40  840

现在我希望我可以说这已经结束并走开......但存在数据输入问题.在第 1 行和第 2 行中，第 1 行的结束时间与第 2 行的开始时间一致，应该合并，因此我的结果如下所示:

Now I wish I could say this was over and walk away...but data entry issues exist. In line 1 and 2, the end time of line 1 lines up with the start time in line 2 and should be combined so my results look like this :

Date     Start  length     end

1 2010-11-11 600    70  670

2 2010-11-11 750    15  765

3 2010-11-11 800    40  840

关于如何构建此逻辑以便我取回 3 行而不是 4 行的任何想法?我现在正在努力在 #sd1.start + #sd1.length = #sd2.start 上将表格加入自己.

Any idea's on how to build this logic so I get the 3 lines back instead of 4? I'm working on joining the table to itself on #sd1.start + #sd1.length = #sd2.start for now.

更复杂的是……上面的例子是需要合并的 2 行.我遇到了一个记录，它有 30 个连续的 1 分钟条目，我需要将其制作成单个记录.幸运的是，它们不能重叠(您不会有 2 条记录占用相同的分钟数)，但我认为我上面考虑的 join 语句对此不起作用.

And to further complicate...the example above was 2 lines that needed combined. I've come across a record that had 30 1 minute entries in succession that I will need to make into a single record. Fortunately they cannot overlap (you won't have 2 records occupying the same minutes), but I don't think the join statement I'm considering above will work for that.

推荐答案

不需要 CTE，您只需要一个辅助表.创建一次，就像这样:

No need for a CTE, all you need is a helper table. Create it once, like so:

Create Table DayMinute(Minute Integer)
Declare @M Integer
Set @M = 1
While (@M <= 24*60)
Begin
  Insert Into DayMinute(Minute) Values(@M)
  Set @M = @M + 1
End

那么，你只需要一点技巧:

Then, all you need is a bit of tricksiness:

Select 
  DM.Minute,
  SD.Sched_ID
Into #MinutesWithException
From 
  DayMinute As DM
  Inner Join #SD As SD
    On DM.Minute Between SD.Start_Minute And SD.Start_Minute + Length

Select
  MWE.Sched_ID,
  SH.[Date],
  SH.Agent_ID,
  [Start_Minute] = MWE.Minute,
  [End_Minute] = (Select Min(Last.Minute) -- First one to have no successor
                  From #MinutesWithException As Last
                  Where Last.Sched_ID = MWE.Sched_ID
                    And Last.Minute > MWE.Minute
                    And Not Exists(Select *
                                   From #MinutesWithException As Next
                                   Where Next.Sched_ID = MWE.Sched_iD
                                     And Next.Minute = Last.Minute + 1))
From 
  #MinutesWithException As MWE
  Inner Join #SH As SH
    On MWE.Sched_ID = SH.Sched_ID
Where
  Not Exists(Select * -- All those without predecessor
             From #MinutesWithException As Previous
             Where Previous.Sched_ID = MWE.Sched_ID
               And Previous.Minute = MWE.Minute - 1)

请记住，许多 SQL 问题可以通过重新措辞来解决.不要问哪些范围没有间隙"，而要问哪些分钟有间隔".其余的从那里开始.

Remember, a lot of SQL problems can be solved by rephrasing them. Don't ask "which ranges do not have a gap", ask "which minutes have an interval". The rest follows from there.

这篇关于T-SQL 脚本 - 时间线的逻辑问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！