logo标签列表

如何在时间间隔上进行计算？

c#sql-serverlinqsql-server-2012intervals
18

我有一个问题，我解决了它，但是我写了一个很长的过程，我不能确定它包含所有可能的情况。

这个问题是：

如果我有一个主时间间隔(从A到B)，以及多个或没有的次时间间隔。

(`From X to Y AND From X` to Y` AND X`` to  Y`` AND ....`)

我希望能够在SQL Server过程和C#方法中，以最少的条件高效地将主时间区间（AB）内的所有部分（不包括次要时间区间）以分钟为单位相加，求出总和。

例如：如果我的主时间区间是从02:00到10:30，并且有一个次要时间区间从04:00到08:00

现在我想要得到这个结果：((04:00 - 02:00) + (10:30 -08:00))* 60

图示例：

在第一个例子中，结果将是：

((X-A) + (B-Y)) * 60

当我有许多次要期间时，情况会更加复杂。

注意:

当我需要将主期间[A，B]与最多两个平行的次要期间集合的并集进行比较时，可能会发生次要期间之间的重叠。第一个集合只包含一个次要期间，而第二个集合包含（多个或零个）次要期间。例如，在比较[A，B]与（2,5）集合的图表中，第一个集合(2)包含一个次要期间，而第二个集合(5)包含三个次要期间。这是最糟糕的情况，我需要处理。

例如：

如果我的主区间是[15:00,19:40]， 并且我有两组次要区间。根据我的规则，其中至少一组应该包含一个次要区间。 比方说，第一组是[11:00,16:00]， 而第二组由两个次要区间[10:00,15:00]，[16:30,17:45]组成。 现在我想得到结果(16:30 -16:00) +(19:40 -17:45)

根据评论：

我的表格如下：

第一个表格包含了次要期间，同一日期特定员工最多有两组次要期间。第一组只包含工作日（W）的一个次要期间[work_st, work_end]，如果这一天是周末（E），则此集合将为空，在这种情况下，次要期间之间不会重叠。而第二组可以包含同一日期内的多个次要期间[check_in，check_out]，因为员工可能在同一天内多次签到/签退。

emp_num  day_date   work_st    work_end   check_in   check_out     day_state

  547    2015-4-1   08:00       16:00     07:45      12:10           W
  547    2015-4-1   08:00       16:00     12:45      17:24           W
  547    2015-4-2   00:00       00:00     07:11      13:11           E
第二个表格包含主时间段[A,B]，这是该员工在当天的一个时间段（一条记录）。
emp_num  day_date   mission_in    mission_out
  547    2015-4-1    15:00          21:30
  547    2015-4-2    8:00           14:00

在前面的示例中，如果我有一个必需的过程或方法，此过程应该接受两个参数：

  • 日期
  • 员工编号

在前面的示例中，应该像这样('2015-4-1' ,547)

根据我的解释：

  • 第二张表中主要期间（任务期间）[A,B]： 对于该日期的员工，此日期应仅有一个期间。

    [15:00,21:30]

  • 对于已传递日期('2015-4-1')的该员工的次要时段： 来自第一张表的两组子时间段（最坏情况）

    第一组应包含一个（或零个）子时间段[08:00,16:00]， 第二组可以包含多个子时间段（或零个）

    [07:45,12:10]，[12:45,17:24]

输出结果应为[17:24,21:30]并转换为分钟

注意

所有day_date、mission_in、mission_out、work_st、work_end、check_in、check_out都是datetime字段， 但为简化起见，我仅在示例中放置了时间，并且希望忽略除day_date外的日期部分，因为它是我基于emp_num计算的日期。

enter image description here

这不就是A-B的长度减去它包含的所有区间的长度，但不能小于0吗？ - Gert Arnold
@GertArnold: 是的，但有时次要间隔的某些部分会超出主周期，例如case 2，因此结果将是(B-Y)，或者存在许多次要间隔或根本没有次要间隔。 - Anyname Donotcare
首先将所有的秒间隔缩小到至少从A开始，最多到B结束。我认为这些秒间隔不会重叠？ - Gert Arnold
多个时间间隔的来源是什么？是单个记录具有多个列，还是每个记录都有自己的起始/结束，或者多个时间间隔根本不是数据库的一部分？ - Robert McKee
1
我现在能够更好地理解问题，但如果您展示一下数据是如何存储的话会更有帮助……如果它目前已经被存储了的话？我认为这个问题可以解决，但任何回答者都将基于对您如何存储数据的假设。一些实际的表结构和虚拟数据将会非常有用。 - Tanner
显示剩余14条评论
5个回答
3

我必须解决这个问题以处理一些调度数据。这允许多个在线时间，但假设它们不重叠。

select convert(datetime,'1/1/2015 5:00 AM') StartDateTime,  convert(datetime,'1/1/2015 5:00 PM') EndDateTime, convert(varchar(20),'Online') IntervalType into #CapacityIntervals
insert into #CapacityIntervals select '1/1/2015 4:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 5:00 AM' StartDateTime,  '1/1/2015 6:00 AM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 10:00 AM' StartDateTime,  '1/1/2015 12:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 11:00 AM' StartDateTime,  '1/1/2015 1:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 4:00 PM' StartDateTime,  '1/1/2015 6:00 PM' EndDateTime, 'Offline' IntervalType
insert into #CapacityIntervals select '1/1/2015 1:30 PM' StartDateTime,  '1/1/2015 2:00 PM' EndDateTime, 'Offline' IntervalType

    --Populate your Offline table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Offline
    from #CapacityIntervals
    where IntervalType in ('Offline','Cleanout')
    group by StartDateTime, EndDateTime

    --Populate your Online table
    select 
        ROW_NUMBER() over (Order by StartDateTime, EndDateTime) Rownum,
        StartDateTime, 
        EndDateTime
    into #Online
    from #CapacityIntervals
    where IntervalType not in ('Offline','Cleanout')


    --If you have overlapping online intervals... check for those here and consolidate.


    -------------------------------
    --find overlaping offline times
    -------------------------------
    declare @Finished as tinyint
    set @Finished = 0

    while @Finished = 0
    Begin
        update #Offline
        set #Offline.EndDateTime = OverlapEndDates.EndDateTime
        from #Offline
        join
            (
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum <= Overlap.Rownum
            group by #Offline.Rownum
            ) OverlapEndDates
        on #Offline.Rownum = OverlapEndDates.Rownum

        --Remove Online times completely inside of online times
        delete #Offline
        from #Offline
        join #Offline Overlap
        on #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
            and #Offline.Rownum > Overlap.Rownum

        --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
        IF NOT EXISTS(
                select #Offline.Rownum,
                    MAX(Overlap.EndDateTime) EndDateTime
                from #Offline
                join #Offline Overlap
                on  Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                    and #Offline.Rownum < Overlap.Rownum
                group by #Offline.Rownum
                )
            SET @Finished = 1
    END

    -------------------------------
    --Modify Online times with offline ranges
    -------------------------------

    --delete any Online times completely inside offline range
    delete #Online 
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range at the beginning
    update #Online
    set #Online.StartDateTime = #Offline.EndDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
        and #Online.EndDateTime >= #Offline.EndDateTime

    --Find Online Times with offline range at the end
    update #Online
    set #Online.EndDateTime = #Offline.StartDateTime
    from #Online
    join #Offline
    on #Online.StartDateTime <= #Offline.StartDateTime
        and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

    --Find Online Times with offline range punched in the middle
    select #Online.Rownum, 
        #Offline.Rownum OfflineRow,
        #Offline.StartDateTime,
        #Offline.EndDateTime,
        ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
    into #OfflineHoles
    from #Online
    join #Offline
    on #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
        and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

    declare @HoleNumber as integer
    select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

    --Punch the holes out of the online times
    While @HoleNumber > 0
    Begin
        insert into #Online 
        select
            -1 Rownum,
            #OfflineHoles.EndDateTime StartDateTime,
            #Online.EndDateTime EndDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        update #Online
        set #Online.EndDateTime = #OfflineHoles.StartDateTime
        from #Online
        join #OfflineHoles
        on #Online.Rownum = #OfflineHoles.Rownum
        where OfflineHoleNumber = @HoleNumber

        set @HoleNumber=@HoleNumber-1
    end

--Output total hours
select SUM(datediff(second,StartDateTime, EndDateTime)) / 3600.0 TotalHr
from #Online

--see how it split up the online intervals
select * 
from #Online
order by StartDateTime, EndDateTime
这段代码应该完全符合你的需求。你可以将员工编号作为所有连接的键。用你的第一张表替换#online，第二张表替换#offline。它应该能够一次性处理整个数据集。它运行良好...我们使用了几个月而没有任何问题。 - Brian Pressler
2

我已经更新了我的答案，并使用您的数据示例，为员工248添加了另一个示例，该示例使用了您图表中的情况2和5。

--load example data for emply 547
select CONVERT(int, 547) emp_num, 
    Convert(datetime, '2015-4-1') day_date, 
    Convert(datetime, '2015-4-1 08:00') work_st,
    Convert(datetime, '2015-4-1 16:00') work_end, 
    Convert(datetime, '2015-4-1 07:45') check_in, 
    Convert(datetime, '2015-4-1 12:10') check_out, 
    'W' day_state
into #SecondaryIntervals
insert into #SecondaryIntervals select 547, '2015-4-1', '2015-4-1 08:00', '2015-4-1 16:00', '2015-4-1 12:45', '2015-4-1 17:24', 'W'
insert into #SecondaryIntervals select 547, '2015-4-2', '2015-4-2 00:00', '2015-4-2 00:00', '2015-4-2 07:11', '2015-4-2 13:11', 'E'

select CONVERT(int, 547) emp_num, 
    Convert(datetime, '2015-4-1') day_date, 
    Convert(datetime, '2015-4-1 15:00') mission_in,
    Convert(datetime, '2015-4-1 21:30') mission_out
into #MainIntervals
insert into #MainIntervals select 547, '2015-4-2', '2015-4-2 8:00', '2015-4-2 14:00'

--load more example data for an employee 548 with overlapping secondary intervals
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 9:00', '2015-4-1 10:00', 'W'
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 10:30', '2015-4-1 12:30', 'W'
insert into #SecondaryIntervals select 548, '2015-4-1', '2015-4-1 06:00', '2015-4-1 11:00', '2015-4-1 13:15', '2015-4-1 16:00', 'W'

insert into #MainIntervals select 548, '2015-4-1', '2015-4-1 8:00', '2015-4-1 14:00'

--Populate your Offline table with the intervals in #SecondaryIntervals
select 
    ROW_NUMBER() over (Order by emp_num, day_date, StartDateTime, EndDateTime) Rownum,
    emp_num,
    day_date,
    StartDateTime, 
    EndDateTime
into #Offline
from 
    (select emp_num,
        day_date,
        work_st StartDateTime, 
        work_end EndDateTime
    from #SecondaryIntervals
    where day_state = 'W'
    Group by emp_num,
        day_date,
        work_st, 
        work_end
    union
    select 
        emp_num,
        day_date,
        check_in StartDateTime, 
        check_out EndDateTime
    from #SecondaryIntervals
    Group by emp_num,
        day_date,
        check_in, 
        check_out
    ) SecondaryIntervals


--Populate your Online table
select 
    ROW_NUMBER() over (Order by emp_num, day_date, mission_in, mission_out) Rownum,
    emp_num,
    day_date,
    mission_in StartDateTime, 
    mission_out EndDateTime
into #Online
from #MainIntervals
group by emp_num,
    day_date,
    mission_in,
    mission_out


-------------------------------
--find overlaping offline times
-------------------------------
declare @Finished as tinyint
set @Finished = 0

while @Finished = 0
Begin
    update #Offline
    set #Offline.EndDateTime = OverlapEndDates.EndDateTime
    from #Offline
    join
        (
        select #Offline.Rownum,
            MAX(Overlap.EndDateTime) EndDateTime
        from #Offline
        join #Offline Overlap
        on #Offline.emp_num = Overlap.emp_num
            and #Offline.day_date = Overlap.day_date
            and Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
            and #Offline.Rownum <= Overlap.Rownum
        group by #Offline.Rownum
        ) OverlapEndDates
    on #Offline.Rownum = OverlapEndDates.Rownum

    --Remove Online times completely inside of online times
    delete #Offline
    from #Offline
    join #Offline Overlap
    on #Offline.emp_num = Overlap.emp_num
        and #Offline.day_date = Overlap.day_date
        and #Offline.StartDateTime between Overlap.StartDateTime and Overlap.EndDateTime
        and #Offline.EndDateTime between Overlap.StartDateTime and Overlap.EndDateTime
        and #Offline.Rownum > Overlap.Rownum

    --LOOK IF THERE ARE ANY MORE CHAINS LEFT    
    IF NOT EXISTS(
            select #Offline.Rownum,
                MAX(Overlap.EndDateTime) EndDateTime
            from #Offline
            join #Offline Overlap
            on #Offline.emp_num = Overlap.emp_num
                and #Offline.day_date = Overlap.day_date
                and Overlap.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
                and #Offline.Rownum < Overlap.Rownum
            group by #Offline.Rownum
            )
        SET @Finished = 1
END

-------------------------------
--Modify Online times with offline ranges
-------------------------------

--delete any Online times completely inside offline range
delete #Online 
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
    and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

--Find Online Times with offline range at the beginning
update #Online
set #Online.StartDateTime = #Offline.EndDateTime
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime between #Offline.StartDateTime and #Offline.EndDateTime
    and #Online.EndDateTime >= #Offline.EndDateTime

--Find Online Times with offline range at the end
update #Online
set #Online.EndDateTime = #Offline.StartDateTime
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Online.StartDateTime <= #Offline.StartDateTime
    and #Online.EndDateTime between #Offline.StartDateTime and #Offline.EndDateTime

--Find Online Times with offline range punched in the middle
select #Online.Rownum, 
    #Offline.Rownum OfflineRow,
    #Offline.StartDateTime,
    #Offline.EndDateTime,
    ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber
into #OfflineHoles
from #Online
join #Offline
on #Online.emp_num = #Offline.emp_num
    and #Online.day_date = #Offline.day_date
    and #Offline.StartDateTime between #Online.StartDateTime and #Online.EndDateTime
    and #Offline.EndDateTime between #Online.StartDateTime and #Online.EndDateTime

declare @HoleNumber as integer
select @HoleNumber = isnull(MAX(OfflineHoleNumber),0) from #OfflineHoles

--Punch the holes out of the online times
While @HoleNumber > 0
Begin
    insert into #Online 
    select
        -1 Rownum,
        #Online.emp_num,
        #Online.day_date,
        #OfflineHoles.EndDateTime StartDateTime,
        #Online.EndDateTime EndDateTime
    from #Online
    join #OfflineHoles
    on #Online.Rownum = #OfflineHoles.Rownum
    where OfflineHoleNumber = @HoleNumber

    update #Online
    set #Online.EndDateTime = #OfflineHoles.StartDateTime
    from #Online
    join #OfflineHoles
    on #Online.Rownum = #OfflineHoles.Rownum
    where OfflineHoleNumber = @HoleNumber

    set @HoleNumber=@HoleNumber-1
end

--Output total hours
select emp_num, day_date, 
    SUM(datediff(second,StartDateTime, EndDateTime)) / 3600.0 TotalHr, 
    SUM(datediff(second,StartDateTime, EndDateTime)) / 60.0 TotalMin
from #Online
group by emp_num, day_date
order by 1, 2

--see how it split up the online intervals
select emp_num, day_date, StartDateTime, EndDateTime
from #Online
order by 1, 2, 3, 4

输出结果为：

emp_num     day_date                TotalHr                                 TotalMin
----------- ----------------------- --------------------------------------- ---------------------------------------
547         2015-04-01 00:00:00.000 4.100000                                246.000000
547         2015-04-02 00:00:00.000 0.816666                                49.000000
548         2015-04-01 00:00:00.000 0.750000                                45.000000

(3 row(s) affected)

emp_num     day_date                StartDateTime           EndDateTime
----------- ----------------------- ----------------------- -----------------------
547         2015-04-01 00:00:00.000 2015-04-01 17:24:00.000 2015-04-01 21:30:00.000
547         2015-04-02 00:00:00.000 2015-04-02 13:11:00.000 2015-04-02 14:00:00.000
548         2015-04-01 00:00:00.000 2015-04-01 12:30:00.000 2015-04-01 13:15:00.000

(3 row(s) affected)

我将保留原先的回答，因为它更加通用，以便其他人也可以使用。我看到你对这个问题添加了悬赏。如果我的回答有什么不满意的地方，请告诉我，我会尽力帮助你。我使用这种方法处理数千个间隔，只需要几秒钟就能返回结果。

我点赞了你们两个的回答，我会在几个案例上测试它们，非常感谢。 - Anyname Donotcare
2
我的解决方案与Vladimir Baranov非常相似。
链接到{{link1：.NetFiddle}}
一般想法
我的算法基于{{link2：interval tree}}的修改。它假设最小时间单位为1分钟（易于修改）。
每个树节点处于3种状态之一：未访问、已访问和已使用。该算法基于递归搜索函数，可以通过以下步骤描述：
  1. 如果节点是使用或搜索间隔为空，则返回空间隔。
  2. 如果节点是未访问且节点间隔等于搜索间隔，则将当前节点标记为已使用并返回节点间隔。
  3. 将节点标记为已访问，分割搜索间隔并返回左右子节点搜索的总和。
解决方案步骤
将以下文本翻译成中文：
  1. 计算最大间隔。
  2. 添加到“次要间隔”树中。
  3. 添加到“主要间隔”树中。

  4. 计算间隔总和。

    请注意，我假设间隔为[start; end]，即两个端点都包含在内，这很容易更改。

要求

假设

n-“次要间隔”的数量

m-基本单位的最大时间

构建需要O（2n）存储空间，并在O（n log n + m）时间内工作。

这是我的代码

  public class Interval
    {
        public int Start { get; set; }

        public int End { get; set; }
    };
    enum Node
    {
        Unvisited = 0,
        Visited = 1,
        Used = 2
    };
    Node[] tree;

    public void Calculate()
    {
        var secondryIntervalsAsDates = new List<Tuple<DateTime,DateTime>> { new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 0, 0), new DateTime(2015, 03, 15, 5, 0, 0))};
        var mainInvtervalAsDate = new Tuple<DateTime, DateTime>(new DateTime(2015, 03, 15, 3, 0, 0), new DateTime(2015, 03, 15, 7, 0, 0));
        // calculate biggest interval
        var startDate = secondryIntervalsAsDates.Union( new List<Tuple<DateTime,DateTime>>{mainInvtervalAsDate}).Min(s => s.Item1).AddMinutes(-1);
        var endDate = secondryIntervalsAsDates.Union(new List<Tuple<DateTime, DateTime>> { mainInvtervalAsDate }).Max(s => s.Item2);
        var mainInvterval = new Interval { Start = (int)(mainInvtervalAsDate.Item1 - startDate).TotalMinutes, End = (int)(mainInvtervalAsDate.Item2 - startDate).TotalMinutes };
        var wholeInterval = new Interval { Start = 1, End = (int)(endDate - startDate).TotalMinutes};
        //convert intervals to minutes
        var secondaryIntervals = secondryIntervalsAsDates.Select(s => new Interval { Start = (int)(s.Item1 - startDate).TotalMinutes, End = (int)(s.Item2 - startDate).TotalMinutes}).ToList();
        tree = new Node[wholeInterval.End * 2 + 1];
        //insert secondary intervals
        secondaryIntervals.ForEach(s => Search(wholeInterval, s, 1));
        //insert main interval
        var result = Search(wholeInterval, mainInvterval, 1);
        //calculate result
        var minutes = result.Sum(r => r.End - r.Start) + result.Count();
    }

    public IEnumerable<Interval> Search(Interval current, Interval searching, int index)
    {
        if (tree[index] == Node.Used || searching.End < searching.Start)
        {
            return new List<Interval>();
        }
        if (tree[index] == Node.Unvisited && current.Start == searching.Start && current.End == searching.End)
        {
            tree[index] = Node.Used;
            return new List<Interval> { current };
        }
        tree[index] = Node.Visited;
        return Search(new Interval { Start = current.Start, End = current.Start + (current.End - current.Start) / 2 },
                  new Interval { Start = searching.Start, End = Math.Min(searching.End, current.Start + (current.End - current.Start) / 2)  }, index * 2).Union(
            Search(new Interval { Start = current.Start + (current.End - current.Start) / 2 + 1 , End = current.End},
              new Interval { Start = Math.Max(searching.Start, current.Start + (current.End - current.Start) / 2 + 1), End = searching.End }, index * 2 + 1));
    }
1
我发现了可能是最简单的解决方案。

.netFiddle | .netFiddle

  1. Sort "Secondary intervals" by start date.
  2. Look for gaps in "secondary intervals" (simple iteration)

  3. Compare gaps with "main interval".

        //declare intervals
var secondryIntervals = new List<Tuple<DateTime, DateTime>> {
        new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 0, 0), new DateTime(2015, 03, 15, 5, 0, 0)),
        new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 10, 0), new DateTime(2015, 03, 15, 4, 40, 0)),
        new Tuple<DateTime, DateTime>( new DateTime(2015, 03, 15, 4, 40, 0), new DateTime(2015, 03, 15, 5, 20, 0))};
var mainInterval = new Tuple<DateTime, DateTime>(new DateTime(2015, 03, 15, 3, 0, 0), new DateTime(2015, 03, 15, 7, 0, 0));
// add two empty intervals before and after main interval
secondryIntervals.Add(new Tuple<DateTime, DateTime>(mainInterval.Item1.AddMinutes(-1), mainInterval.Item1.AddMinutes(-1)));
secondryIntervals.Add(new Tuple<DateTime, DateTime>(mainInterval.Item2.AddMinutes(1), mainInterval.Item2.AddMinutes(1)));
secondryIntervals = secondryIntervals.OrderBy(s => s.Item1).ToList();
// endDate will rember 'biggest' end date
var endDate = secondryIntervals.First().Item1;
var result = secondryIntervals.Select(s =>
{
    var temp = endDate;
    endDate = endDate < s.Item2 ? s.Item2 : endDate;
    if (s.Item1 > temp)
    {
        return new Tuple<DateTime, DateTime>(temp < mainInterval.Item1 ? mainInterval.Item1 : temp,
                                             mainInterval.Item2 < s.Item1 ? mainInterval.Item2 : s.Item1);
    }
    return null;
})
    // remove empty records
                .Where(s => s != null && s.Item2 > s.Item1).ToList();
var minutes = result.Sum(s => (s.Item2 - s.Item1).TotalMinutes);
该算法需要 O(n log n) 的时间（用于排序），不需要额外的存储和假设。
1

以下是完整查询的SQLFiddle。

我将展示如何构建一个查询，返回每个emp_num, day_date的分钟数。如果某个emp_num, day_date没有剩余分钟，则结果不会有一行记录为0，根本不会有这样的行。

总体思路

我将使用数字表。我们只需要24*60=1440个数字，但将其存储在数据库中供其他报告使用是一个好主意。我个人拥有10万行。以下是一个非常好的文章，比较了生成此类表的不同方法。

对于每个时间间隔，我将使用数字表生成一组行 - 每分钟一个行。我假设间隔是 [start; end)，即开始分钟包含在内，结束分钟不包含在内。例如，从 07:00 到 08:00 的间隔是 60 分钟，而不是 61。
生成数字表格
DECLARE @Numbers TABLE (N int);
INSERT INTO @Numbers(N)
SELECT TOP(24*60)
    ROW_NUMBER() OVER(ORDER BY S.object_id) - 1 AS N
FROM
    sys.all_objects AS S
ORDER BY N
;

对于这个任务，最好使用从0开始的数字。通常情况下，您会将其作为具有主键 N 的永久表。

示例数据

DECLARE @Missions TABLE (emp_num int, day_date datetime, mission_in datetime, mission_out datetime);
DECLARE @Periods TABLE (emp_num int, day_date datetime, work_st datetime, work_end datetime, check_in datetime, check_out datetime, day_state char(1));

INSERT INTO @Missions (emp_num, day_date, mission_in, mission_out) VALUES
(547, '2015-04-01', '2015-04-01 15:00:00', '2015-04-01 21:30:00'),
(547, '2015-04-02', '2015-04-02 08:00:00', '2015-04-02 14:00:00');

INSERT INTO @Periods (emp_num, day_date, work_st, work_end, check_in, check_out, day_state) VALUES
(547, '2015-04-01', '2015-04-01 08:00:00', '2015-04-01 16:00:00', '2015-04-01 07:45:00', '2015-04-01 12:10:00', 'W'),
(547, '2015-04-01', '2015-04-01 08:00:00', '2015-04-01 16:00:00', '2015-04-01 12:45:00', '2015-04-01 17:24:00', 'W'),
(547, '2015-04-02', '2015-04-02 00:00:00', '2015-04-02 00:00:00', '2015-04-02 07:11:00', '2015-04-02 13:11:00', 'E');

我的解决方案不使用day_state列。我期望你在work_st和work_end中都有00:00:00。解决方案期望同一行内的日期组件相同，并且day_date没有时间组件。
如果我为这个任务设计模式，我会有三个表而不是两个：Missions、WorkPeriods和CheckPeriods。我会将你的Periods表拆分成两个，以避免在几行中重复使用work_st和work_end。但是这个解决方案将处理你当前的模式，并且本质上会动态生成第三个表。实际上，这意味着性能可能会得到改善。 任务分钟数 
WITH
CTE_MissionMinutes
AS
(
    SELECT emp_num, day_date, N.N
    FROM
        @Missions AS M
        CROSS JOIN @Numbers AS N
    WHERE
        N.N >= DATEDIFF(minute, M.day_date, M.mission_in) AND
        N.N < DATEDIFF(minute, M.day_date, M.mission_out)
)

每一行来自@Missions的原始数据将转换为一组行，其中每分钟都有一行，该行位于区间(mission_in, mission_out)内。 工作时段 
,CTE_WorkPeriods
AS
(
    SELECT P.emp_num, P.day_date, P.work_st, P.work_end
    FROM @Periods AS P
    GROUP BY P.emp_num, P.day_date, P.work_st, P.work_end
)

生成第三个帮助表格 - 每行对应一个emp_num, day_date, work_st, work_end，表示(work_st, work_end)的所有时间段。
工作和检查分钟。
,CTE_WorkMinutes
AS
(
    SELECT emp_num, day_date, N.N
    FROM
        CTE_WorkPeriods
        CROSS JOIN @Numbers AS N
    WHERE
        N.N >= DATEDIFF(minute, CTE_WorkPeriods.day_date, CTE_WorkPeriods.work_st) AND
        N.N < DATEDIFF(minute, CTE_WorkPeriods.day_date, CTE_WorkPeriods.work_end)
)
,CTE_CheckMinutes
AS
(
    SELECT emp_num, day_date, N.N
    FROM
        @Periods AS P
        CROSS JOIN @Numbers AS N
    WHERE
        N.N >= DATEDIFF(minute, P.day_date, P.check_in) AND
        N.N < DATEDIFF(minute, P.day_date, P.check_out)
)

和 Missions 完全相同。

联合 "次级区间"

,CTE_UnionPeriodMinutes
AS
(
    SELECT emp_num, day_date, N
    FROM CTE_WorkMinutes

    UNION ALL -- can be not ALL here, but ALL is usually faster

    SELECT emp_num, day_date, N
    FROM CTE_CheckMinutes
)

从主要间隔中减去次要间隔。
,CTE_FinalMinutes
AS
(
    SELECT emp_num, day_date, N
    FROM CTE_MissionMinutes

    EXCEPT

    SELECT emp_num, day_date, N
    FROM CTE_UnionPeriodMinutes
)

总结分钟数。
SELECT
    emp_num
    ,day_date
    ,COUNT(*) AS FinalMinutes
FROM CTE_FinalMinutes
GROUP BY emp_num, day_date
ORDER BY emp_num, day_date;

将所有CTE组合在一起即可生成最终查询。 结果集 
emp_num day_date                FinalMinutes
547     2015-04-01 00:00:00.000 246
547     2015-04-02 00:00:00.000 49

There are 246 minutes between 17:24 and 21:30.
There are  49 minutes between 13:11 and 14:00.

这里是SQLFiddle，包含完整的查询语句。

展示导致这个SUM分钟数的实际时间间隔相当简单，但你说你只需要这个SUM。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接