多个日期范围之间的分钟数之和

本文介绍了多个日期范围之间的分钟数之和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

该方案是用户指定何时可用，这些指定时间可以重叠彼此。我试图获得可用的总时间。使用SQL小提琴的示例：

The scenario is users specify when they are available, these specified times can overlap each other. I'm trying to get the total time they are available for. Example with SQL Fiddle:

--Available--
ID  userID  availStart          availEnd
1   456     '2012-11-19 16:00'  '2012-11-19 17:00'
2   456     '2012-11-19 16:00'  '2012-11-19 16:50'
3   456     '2012-11-19 18:00'  '2012-11-19 18:30'
4   456     '2012-11-19 17:30'  '2012-11-19 18:10'
5   456     '2012-11-19 16:00'  '2012-11-19 17:10'
6   456     '2012-11-19 16:00'  '2012-11-19 16:50'

输出应为130分钟：

1: 60
2: 0 as falls inside 1
3: 30
4: 30 as the last 10 mins is covered by 3
5: 10 as first 60 mins is covered by 1
6: 0 as falls inside 1

我可以得到总共重复的分钟数，但这超过了可用分钟的SUM：

I can get the total overlapping minutes, however this is more than the SUM of the available minutes:

任何想法如何实现？

编辑11月12日21：谢谢到目前为止所有人的解决方案 - 在某种程度上，我很高兴看到这不是一个'容易'的查询写。

EDIT 21st Nov 12: Thanks so far for everyone's solutions - in a way I'm happy to see this wasn't an 'easy' query to write.

EDIT 23rd Nov 12 ：这是伟大的工作。在内部，我们认为可能最好确保用户不能输入重叠时间（例如强制他们修改现有条目）！

EDIT 23rd Nov 12: This is all great work. Internally here we're thinking it might be best to ensure users cannot enter overlapping times (eg forcing them to amend an existing entry)!

推荐答案

有一个

我已经做了一些

在进行一些性能分析后，这里是一个混合CTE /表变量版本，除了基于游标的方法之外，其性能要优于任何值。

After doing some performance analysis, here's a hybrid CTE/table variable version that performs better than anything except the cursor based approach

Create Function dbo.AvailMinutesHybrid(@UserID int) Returns Int As
Begin

Declare @UserRanges Table (
  RN int not null primary key,
  AvailStart datetime,
  AvailEnd datetime
)
Declare @Ret int = Null

;With OrderedRanges as (
  Select
    Row_Number() Over (Partition By UserID Order By AvailStart) AS RN,
    AvailStart,
    AvailEnd
  From
    dbo.Available
  Where
    UserID = @UserID
)
Insert Into @UserRanges Select * From OrderedRanges


;With AccumulateMinutes (RN,Accum, CurStart, CurEnd) as (
  Select
    RN, 0, AvailStart, AvailEnd
  From
    @UserRanges
  Where
    RN = 1
  Union All
  Select
    o.RN,
    a.Accum + Case When o.AvailStart <= a.CurEnd Then
        0
      Else
        DateDiff(Minute, a.CurStart, a.CurEnd)
      End,
    Case When o.AvailStart <= a.CurEnd Then
        a.CurStart
      Else
        o.AvailStart
      End,
    Case When o.AvailStart <= a.CurEnd Then
        Case When a.CurEnd > o.AvailEnd Then a.CurEnd Else o.AvailEnd End
      Else
        o.AvailEnd
      End
  From
    AccumulateMinutes a
        Inner Join
    @UserRanges o On
        a.RN + 1 = o.RN
)

Select
  @Ret = Max(Accum + datediff(Minute, CurStart, CurEnd))
From
  AccumulateMinutes
Option
  (MaxRecursion 0)

Return @Ret

End

这篇关于多个日期范围之间的分钟数之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

index