如何进行时间间隔的计算? [英] How to make calculation on time intervals?
问题描述
我有一个问题，我解决了它，但我写了一个很长的程序，我不能确定它涵盖了所有可能的情况.
问题:
如果我有一个主要间隔时间(从A到B)和次要间隔时间(很多或没有)
(`From X to Y AND From X` to Y` AND X`` to Y`` AND ....`)我想在分钟内以有效且最少的次数SUM我的主要间隔时间(AB)排除次要间隔的所有部分条件(SQL server程序和C#方法)?
例如:如果我的主要时间间隔从 02:00 到 10:30并说一个次要间隔从 04:00 到 08:00
现在我想要这个结果:((04:00 - 02:00) + (10:30 -08:00))* 60
图形示例:
在第一种情况下，结果将是:
((X-A) + (B-Y)) * 60当我有很多次要期间会更复杂.
注意:
可能只有当我必须将主要时期 [A,B] 与 UNION 进行比较时，次要间隔之间才会发生重叠至多两组平行的次要间隔em> .第一组必须只包含一个次要间隔，第二组包含(许多或没有)次要间隔.例如在比较 [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]，因为员工可能在同一天多次check_in_out.
emp_num day_date work_st work_end check_in checkout day_state547 2015-4-1 08:00 16:00 07:45 12:10 W547 2015-4-1 08:00 16:00 12:45 17:24 W547 2015-4-2 00:00 00:00 07:11 13:11 E第二个表包含主要期间[A,B] 并且是该员工当天的一个期间(一条记录)
emp_num day_datemission_inmission_out547 2015-4-1 15:00 21:30547 2015-4-2 8:00 14:00<小时>
在前面的例子中，如果我有一个需要的过程或方法，这个过程应该有两个参数:
- 日期
- emp_num
在前面的例子中应该是这样的 ('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.
我已经用您的数据示例更新了我的答案，并且我正在为员工 248 添加另一个示例，该示例使用您图表中的案例 2 和案例 5.
>
--为员工 547 加载示例数据选择 CONVERT(int, 547) emp_num,转换(日期时间，'2015-4-1')day_date，转换(日期时间，'2015-4-1 08:00')work_st，转换(日期时间，'2015-4-1 16:00')work_end，转换(日期时间，'2015-4-1 07:45')签入，转换(日期时间，'2015-4-1 12:10')签出，'W' day_state进入#SecondaryIntervals插入 #SecondaryIntervals 选择 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'插入 #SecondaryIntervals 选择 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'选择 CONVERT(int, 547) emp_num,转换(日期时间，'2015-4-1')day_date，转换(日期时间，'2015-4-1 15:00')mission_in，转换(日期时间，'2015-4-1 21:30')mission_out进入#MainIntervals插入#MainIntervals select 547, '2015-4-2', '2015-4-2 8:00', '2015-4-2 14:00'-- 为员工 548 加载更多示例数据，其次要间隔重叠插入#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'插入#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'插入#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'插入#MainIntervals select 548, '2015-4-1', '2015-4-1 8:00', '2015-4-1 14:00'-- 使用#SecondaryIntervals 中的间隔填充离线表选择ROW_NUMBER() over (Order by emp_num, day_date, StartDateTime, EndDateTime) Rownum,emp_num,日期，开始日期时间，结束日期时间进入#离线从(选择 emp_num，日期，work_st 开始日期时间，work_end 结束日期时间来自#SecondaryIntervals其中 day_state = 'W'按 emp_num 分组，日期，工作_st，工作结束联盟选择emp_num,日期，签入开始日期时间，结帐结束日期时间来自#SecondaryIntervals按 emp_num 分组，日期，报到，查看) 次要区间--填充您的在线表格选择ROW_NUMBER() over (Order by emp_num, day_date,mission_in,mission_out) Rownum,emp_num,日期，Mission_in 开始日期时间，Mission_out 结束日期时间进入#Online来自#MainIntervals按 emp_num 分组，日期，使命_在，任务输出---------------------------------查找重叠的离线时间-------------------------------将@Finished 声明为 tinyint设置@Finished = 0而@Finished = 0开始更新#离线设置 #Offline.EndDateTime = OverlapEndDates.EndDateTime来自#离线加入(选择#Offline.Rownum，MAX(Overlap.EndDateTime) EndDateTime来自#离线加入#Offline Overlap#Offline.emp_num = Overlap.emp_num和 #Offline.day_date = Overlap.day_date#Offline.StartDateTime 和 #Offline.EndDateTime 之间的 Overlap.StartDateTime和 #Offline.Rownum <= Overlap.Rownum按#Offline.Rownum 分组) 重叠结束日期#Offline.Rownum = OverlapEndDates.Rownum--完全删除在线时间内的在线时间删除#离线来自#离线加入#Offline Overlap#Offline.emp_num = Overlap.emp_num和 #Offline.day_date = Overlap.day_date和 Overlap.StartDateTime 和 Overlap.EndDateTime 之间的 #Offline.StartDateTime和 Overlap.StartDateTime 和 Overlap.EndDateTime 之间的 #Offline.EndDateTime和#Offline.Rownum >重叠行数--看看还有没有链条如果不存在(选择#Offline.Rownum，MAX(Overlap.EndDateTime) EndDateTime来自#离线加入#Offline Overlap#Offline.emp_num = Overlap.emp_num和 #Offline.day_date = Overlap.day_date#Offline.StartDateTime 和 #Offline.EndDateTime 之间的 Overlap.StartDateTime和#Offline.Rownum <重叠行数按#Offline.Rownum 分组)设置@Finished = 1结尾---------------------------------使用离线范围修改在线时间---------------------------------完全删除离线范围内的任何在线时间删除#在线来自#Online加入#离线在#Online.emp_num = #Offline.emp_num和#Online.day_date = #Offline.day_date和#Offline.StartDateTime 和#Offline.EndDateTime 之间的#Online.StartDateTime和#Offline.StartDateTime 和#Offline.EndDateTime 之间的#Online.EndDateTime--以离线范围开始查找在线时间更新#在线设置 #Online.StartDateTime = #Offline.EndDateTime来自#Online加入#离线在#Online.emp_num = #Offline.emp_num和#Online.day_date = #Offline.day_date和#Offline.StartDateTime 和#Offline.EndDateTime 之间的#Online.StartDateTime和 #Online.EndDateTime >= #Offline.EndDateTime--在末尾查找具有离线范围的在线时间更新#在线设置 #Online.EndDateTime = #Offline.StartDateTime来自#Online加入#离线在#Online.emp_num = #Offline.emp_num和#Online.day_date = #Offline.day_date和 #Online.StartDateTime <= #Offline.StartDateTime和#Offline.StartDateTime 和#Offline.EndDateTime 之间的#Online.EndDateTime--查找在中间打孔的离线范围的在线时间选择#Online.Rownum，#Offline.Rownum OfflineRow，#Offline.StartDateTime,#Offline.EndDateTime，ROW_NUMBER() over (Partition by #Online.Rownum order by #Offline.Rownum Desc) OfflineHoleNumber进入#OfflineHoles来自#Online加入#离线在#Online.emp_num = #Offline.emp_num和#Online.day_date = #Offline.day_date以及#Online.StartDateTime 和#Online.EndDateTime 之间的#Offline.StartDateTime以及#Online.StartDateTime 和#Online.EndDateTime 之间的#Offline.EndDateTime将@HoleNumber 声明为整数从 #OfflineHoles 中选择 @HoleNumber = isnull(MAX(OfflineHoleNumber),0)--打出在线时间的漏洞虽然@HoleNumber >0开始插入#Online选择-1 行数，#Online.emp_num,#Online.day_date，#OfflineHoles.EndDateTime StartDateTime,#Online.EndDateTime EndDateTime来自#Online加入#OfflineHoles#Online.Rownum = #OfflineHoles.Rownum其中 OfflineHoleNumber = @HoleNumber更新#在线设置 #Online.EndDateTime = #OfflineHoles.StartDateTime来自#Online加入#OfflineHoles#Online.Rownum = #OfflineHoles.Rownum其中 OfflineHoleNumber = @HoleNumber设置@HoleNumber=@HoleNumber-1结尾--输出总小时数选择 emp_num、day_date、SUM(datediff(second,StartDateTime, EndDateTime))/3600.0 TotalHr,SUM(datediff(second,StartDateTime, EndDateTime))/60.0 TotalMin来自#Online按 emp_num、day_date 分组按 1, 2 排序--看看它是如何分割在线间隔的选择 emp_num、day_date、StartDateTime、EndDateTime来自#Online按 1, 2, 3, 4 排序输出为:
emp_num day_date TotalHr TotalMin——————————————————————————————----------------------- ---------------------------------------547 2015-04-01 00:00:00.000 4.100000 246.000000547 2015-04-02 00:00:00.000 0.816666 49.000000548 2015-04-01 00:00:00.000 0.750000 45.000000(3 行受影响)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.000547 2015-04-02 00:00:00.000 2015-04-02 13:11:00.000 2015-04-02 14:00:00.000548 2015-04-01 00:00:00.000 2015-04-01 12:30:00.000 2015-04-01 13:15:00.000(3 行受影响)我留下了我的另一个答案，因为它更通用，以防其他人想要抓住它.我看到你为这个问题增加了悬赏.如果我的回答有什么让您不满意的地方，请告诉我，我会尽力帮助您.我用这个方法处理了数千个间隔，它在几秒钟内返回.
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 .
The problem:
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 ....`)
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)?
For Example : If my Main interval From 02:00 to 10:30
And say one secondary interval From 04:00 to 08:00
Now i want this result : ((04:00 - 02:00) + (10:30 -08:00))* 60
Example with graph :
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.
NOTE:
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.
For example :
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)
According to the comments :
My table is like this :
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
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 :
- The Date
- The emp_num
in the previous example it should be like this ('2015-4-1' ,547)
According to my explanation :
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 tableThe 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]
The output should be [17:24,21:30] converted to minutes
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.
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
Output is:
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屋！
