计算多个日期范围内的天数 [英] count number of days in multiple date ranges
问题描述
大家好，
我怀疑对于很多人来说这是一个相当简单的问题，但是让我感到难过。
I suspect this is a fairly simple question for a number of people to answer, but it has me stumped.
我有一张桌子，有多个关闭和重新开放日期的各种道路。我想要做的是计算特定道路在一段时间内关闭的总天数。现在这是我迷路的地方.....在一段时间内
的时间 - 即01/01/10 - 05/10/10道路可以在一个或多个地点关闭，我只想计算它关闭的实际天数并从我的计数中删除重叠的天数
I have a table that has multiple closed and reopened dates for a various roads. What i am looking to do is to count the total number of days a particular road is closed during a certain period of time. Now this is where i get lost..... during a set period of time - ie 01/01/10 - 05/10/10 the road could be closed in one or multiple locations and i want to only count the actual days it was closed and remove the overlapped days from my count
EG -
已关闭 ; 打开        NumDaysClosed
Closed Opened NumDaysClosed
01/02/10 05/02/10 NBSP;       4
01/02/10 05/02/10 4
03/03/10 15/03/10     12
03/03/10 15/03/10 12
14/03/10 17/03/10 NBSP;       3
14/03/10 17/03/10 3
从这个例子中我们有1天重叠在这里，很容易看到而不编写代码，但是当你在给定时期内有25-30个日期，其中各个部分被打开或关闭它变得非常棘手 - 但如果有人可以告诉我如何
解决上面的示例我可以调整它来解决我的其余问题
From this example we have 1 day that overlaps here and it is easy to see without writing code, but when you have 25 - 30 dates within a given period where various sections are opened or closed it becomes really tricky - but if someone can show me how to solve the above sample i can adapt it to solve the rest of my problem
任何想法都将不胜感激 - 我一直在努力工作这几天，我似乎无法解决这个问题。
Any thoughts would be greatly appreciated - i have been trying to work this one out for several weeks and i can't seem to get this solved.
TIA
Justin
推荐答案
大家好，
Hi all,
我怀疑对于很多人来说这是一个相当简单的问题，但是我很难过。
I suspect this is a fairly simple question for a number of people to answer, but it has me stumped.
我有一个有多个关闭和重新开放日期的表适用于各种道路。我想要做的是计算特定道路在一段时间内关闭的总天数。现在这是我迷路的地方.....在一段时间内
的时间 - 即01/01/10 - 05/10/10道路可以在一个或多个地点关闭，我只想计算它关闭的实际天数并从我的计数中删除重叠的天数
I have a table that has multiple closed and reopened dates for a various roads. What i am looking to do is to count the total number of days a particular road is closed during a certain period of time. Now this is where i get lost..... during a set period of time - ie 01/01/10 - 05/10/10 the road could be closed in one or multiple locations and i want to only count the actual days it was closed and remove the overlapped days from my count
EG -
已关闭 ; 打开        NumDaysClosed
Closed Opened NumDaysClosed
01/02/10 05/02/10 NBSP;       4
01/02/10 05/02/10 4
03/03/10 15/03/10     12
03/03/10 15/03/10 12
14/03/10 17/03/10 NBSP;       3
14/03/10 17/03/10 3
从这个例子中我们有1天重叠在这里，很容易看到而不编写代码，但是当你在给定时期内有25-30个日期，其中各个部分被打开或关闭它变得非常棘手 - 但如果有人可以告诉我如何
解决上面的示例我可以调整它来解决我的其余问题
From this example we have 1 day that overlaps here and it is easy to see without writing code, but when you have 25 - 30 dates within a given period where various sections are opened or closed it becomes really tricky - but if someone can show me how to solve the above sample i can adapt it to solve the rest of my problem
任何想法都将不胜感激 - 我一直在努力工作这几天，我似乎无法解决这个问题。
Any thoughts would be greatly appreciated - i have been trying to work this one out for several weeks and i can't seem to get this solved.
TIA
Justin
最简单的方法 是有一个包含与道路相关的记录的表格。 ID，RoadID，ClosedDate等。如果有正式的方式来指示应该是字段的部分。写一条记录 每一天 道路已经关闭。
The easiest way is to have a table containing records related to the road. ID,RoadID,ClosedDate, etc.If there is a formal way to indicate the section that should be a field. Write one record for each day the road is closed.
然后得到你的回答只是在日期范围内分组和计算的问题。
Then getting your answer is just a matter of grouping and counting within a date range.
Anyt5hing else将需要选择所有StartDatrs在所需范围内并循环访问代码。将每个关闭日期添加到数组或表后，确保它已经不存在。 如果您通过
查看大量记录，这将会减慢速度。
Anyt5hing else will require selecting all StartDatrs within the desired range and looping through code. Add each closed date to an array or table after making sure it does not exist there already. This will slow things way down if you are lookng through a lot of records.
这篇关于计算多个日期范围内的天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
