如何使时间间隔计算？ [英] How to make calculation on time intervals?
问题描述
我有一个问题，我解决这个问题，但我写了一个很长的过程，我不能肯定它涵盖了所有的可能的情况。
I have a problem ,i solve it but i have written a long procedure and i can't be sure that it covers all the possible cases .
问题
如果我有一个主要间隔时间（从A到b ），和二次间隔时间（很多甚至没有）
If i have a main interval time (From A to B), and secondary interval times (Many or no)
(`From X to Y AND From X` to Y` AND X`` to Y`` AND ....`)
我要 SUM （AB）我的主要时间间隔各地的出的分高效和条件的最少数量二次间隔（SQL Server过程？和C＃的方法）
I want to SUM all parts of My Main interval time (AB) out of secondary intervals in minutes in efficient and the least number of conditions (SQL server Procedure and C# method)?
例如：如果我的主要时间间隔从 2时00分至10:30
和说，一个二级间隔从 04:00至08:00
For Example : If my Main interval From 02:00 to 10:30
And say one secondary interval From 04:00 to 08:00
现在我想这样的结果：（（04:00 - 02:00）+（10:30 -08：00））* 60
Now i want this result : ((04:00 - 02:00) + (10:30 -08:00))* 60
与图形例如：
在第一种情况下，结果将是：
in the first case the result will be :
((X-A) + (B-Y)) * 60
和这将是更加复杂，当我。有很多二次时期
and it will be more complicated when i have many secondary periods.
可能是二次的时间间隔中发生的事情，只有当我有重叠比较的主要时期[A，B]以在 UNION 最多两个平行组次间隔的。第一组有只包含一个次要间隔和在第二组包含二次的时间间隔。例如在比较图中（很多甚至没有） [A，B] 来台（套 2.5 ）的第一套（2）由一个次要间隔和第二组的（5）由三个次要区间。以及这个的最最坏的情况下，我需要处理
May be the overlap among the secondary intervals happening only when i have to compare the main period [A,B] to the UNION of at most two parallel sets of secondary intervals .the first set have to contain only one secondary interval and the the second set contains (many or no ) of secondary intervals .For example in the graph comparing [A,B] to (sets of 2,5)the first set (2) consists of one secondary interval and the second set (5) consists of three secondary intervals . and this 's the most worst case ,i need to handle.
例如：
如果我的主间隔 [15：00,19：40]
和我有两套二次的时间间隔。据我的规则，这些设置应包括的至少一个一个次要间隔。
说，第一套是 [11:00，16：00]
与第二组由说的两个次级间隔 [10：00,15：00]，[16：30,17：45]
现在我想要的结果（18:00 -16： 00）+（19:40 -17：45）
IF my main interval is [15:00,19:40]
and i have two sets of secondary intervals .according to my rule at least one of these sets should consists of one secondary interval.
say the first set is [11:00 ,16:00]
and the second set is consists of say two secondary intervals [10:00,15:00],[16:30,17:45]
Now i want the result (16:30 -16:00) +(19:40 -17:45)
根据该意见：
我的表是这样的：
第一个表中包含次要时段，最多两套二级期在同日特定雇员。第一套包含了工作日（W） [work_st，work_end] ，这种集合只有一个次级周期如果一天是次要时段中的周末 [E] 在这种情况下没有重叠将是空的。和第二组可以包含在相同的日期 [CHECK_IN，CHECK_OUT] ，因为员工可能在同一天check_in_out多次
The first table contains secondary periods ,at most two sets of secondary periods in the same date for specific employee. the first set contains only one secondary period in the work day (W) [work_st,work_end],and this set will be empty if the day is weekend [E] and in this case no overlap among the secondary periods. and the second set may contain many secondary periods in the same date [check_in,check_out] ,because the employee may check_in_out many times in the same day.
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] 和它在这一天（一个记录）一期，该员工
The second table contains the main period[A,B] and it's a one period for this employee at that day (one record)
emp_num day_date mission_in mission_out
547 2015-4-1 15:00 21:30
547 2015-4-2 8:00 14:00
在前面的例子中，如果我有一个过程或方法需要这个过程应该采取两个参数：
In the previous example if i have a procedure or method as required this procedure should take two parameters :
- 的日期
- 在前面的emp_num
例如，它应该是这样的（'情节中字'，547）
in the previous example it should be like this ('2015-4-1' ,547)
据我的解释：
-
的主要时期（任务周期）
[A，b]第二个表：
要在这个日期只是一个周期为员工
The Main period (Mission Period)
[A,B]from the second table : Should be only one period in this date for that employee
[15：00,21：30]
在传递日期的次级周期（'情节中字'）该雇员是二
组从第一个表次级周期（最坏情况下）的
The secondary period for the passed date ('2015-4-1') for that employee was two
sets of secondary periods (the worst case) from the first table
第一组应该只包含一个二级期（或零
段） [08：00,16：00] 第二组可以包含许多次要
段（或零周期）
The first set should contain only one secondary period (or zero
periods) [08:00,16:00] the second set could contain many secondary
periods (or zero periods)
[07：45,12：10] ， [12：45,17：24 ]
输出应该是[17:24 ，21：30]转化为分钟
The output should be [17:24,21:30] converted to minutes
所有 day_date，mission_in，mission_out，work_st，work_end，CHECK_IN，CHECK_OUT
的日期时间字段，但我把刚才的时候在简化的例子，我想忽略除日期部分的 day_date ，因为它是我计算除了 emp_num <基于日期/ code>。
Note
all day_date,mission_in,mission_out,work_st,work_end,check_in,check_out
are datetime fields but i put just the time in the example for simplification , i want to ignore the date part except the day_date because it's the date which i calculate based on in addition to the emp_num.
推荐答案
我已经更新了我的答案与您的数据的例子，我加入另一个例子为雇员248使用案例2和5从图
I've updated my answer with your data example and I'm adding another example for an employee 248 that uses case 2 and 5 from your graph.
--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)
我把其他的答案贴，因为它更通用的情况下，其他人想要分得它。我看你添加一个赏金对这一问题。让我知道，如果有具体谈谈我的回答的东西，不能满足你，我会尽力帮助你。我处理成千上万的间隔用这种方法，它在短短的几秒钟内返回。
I left my other answer posted because it's more generic in case someone else wants to snag it. I see you added a bounty to this question. Let me know if there's something specific about my answer that doesn't satisfy you and I'll try to help you out. I process thousands of intervals with this method and it returns in just a few seconds.
这篇关于如何使时间间隔计算？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
