T-SQL脚本 - 时间轴的逻辑问题

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).

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

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)

一点背景 - #SH标题表将人员计划保存为“Start_minute”,并以分钟为单位进行“schedule_length”。例如,如果开始分钟和时间表长度均为480,那么将显示为上午8点(上午8点=第480分钟)并持续到下午4点(480分钟后,下午4点=第960分钟)

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).

#SD表保存标头的例外。在上面的示例中,该人可能会有一个午餐例外,即start_minute = 720和30(12:00 - 12:30)。

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.

Date和agent_id是我对#SH唯一感兴趣的东西,#sd中的异常信息是我感兴趣的。

This query works:

此查询有效:

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 is ultimately a calculated value of start+length = end

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

This returns something like:

这返回类似于:

   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

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 :

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

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

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.

关于如何构建这个逻辑的任何想法让我得到3行而不是4行?我正在努力在#sd1.start + #sd1.length =#sd2.start上加入表格。

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.

并且进一步复杂化...上面的例子是需要组合的2行。我遇到了一个连续30分钟的记录,我需要将其记录到单个记录中。幸运的是它们不能重叠(你不会有2条记录占用相同的分钟数),但我认为我上面考虑的连接语句不会起作用。

2 个解决方案

#1

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

不需要CTE,您只需要一个帮助表。创建一次,如下所示:

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)

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.

请记住,通过改写它们可以解决许多SQL问题。不要问“哪个范围没有差距”,问“哪个分钟有间隔”。其余部分来自那里。

#2

If you use a recursive CTE to combine the results of the query you have above, you could chain up to 32767 records together. You might consider that approach if you don't think you'll ever get near that amount.

如果使用递归CTE来组合上面的查询结果,则可以将最多32767条记录链接在一起。如果你认为自己不会接近这个数额,你可能会考虑这种方法。

I created a working example, because I wasn't sure. Your grouping would be different, but this is the general idea:

我创建了一个工作示例,因为我不确定。你的分组会有所不同,但这是一般的想法:

CREATE TABLE times
(
[Date] datetime,
[start] int,
[length] int,
[end] int
)
INSERT INTO times([Date], [Start], [length], [End])
VALUES ('11/11/2010',600,30,630)
INSERT INTO times([Date], [Start], [length], [End])
VALUES ('11/11/2010',630,40,670)
INSERT INTO times([Date], [Start], [length], [End])
VALUES ('11/11/2010',750,15,765)
INSERT INTO times([Date], [Start], [length], [End])
VALUES ('11/11/2010',800,40,840)

;WITH chaintimes AS
(
    SELECT t1.Date, t1.start, t1.length, t1.[end]
    FROM times t1 LEFT JOIN times t2 ON t1.start = t2.[end]
    WHERE t2.[end] IS NULL
    UNION ALL
    SELECT times.Date, chaintimes.start, chaintimes.length + times.length AS length, times.[end]
    FROM times INNER JOIN chaintimes ON times.start = chaintimes.[end]
)
, start_maxlength AS
(
    SELECT date, start, max(length) AS maxlength
    FROM chaintimes
    group by date, start
)
SELECT * FROM chaintimes ct
INNER JOIN start_maxlength ml
ON ct.Date = ml.Date AND ct.start = ml.start AND ct.length = ml.maxlength

#1