MsAccess SQL计算重叠时段中的天数，其中一些排除时段 [英] MsAccess SQL count days in overlaping periods with some excluding periods

问题描述

问题:

给定一个表，该表包含给定人员可能(或可能不重叠)的时间段，并给出另一个具有排除条件的表，我想计算每个人的天数和期间(不包括重叠天数和排除时间)期间.

Given a table with periods of time for a given person that may (or may not) overlap and given another table with exclusions, I want to count the number of days for each person and period excluding the overlapping days and also the exclusion periods.

当他们说一张图片时，它值1000个字，所以:

As they say an image it's worth 1000 words so:

• 绿色涂上句点
• 红色表示排除期
• 解决方案以蓝色绘制(预期输出，每个周期在最终查询中应为一行)，所以我可以datediff("d", end_date, start_date)

示例场景:

期间表:

create table periods (
  `id` COUNTER (1,1) PRIMARY KEY,
  `person_id` text(50),
  `start_date` Date,
  `end_date` Date
)

排除期表:

create table exclusions (
  `start_date` As Date,
  `end_date` As Date
)

还有一些值:

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('01/09/2014'), CDate('30/09/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('10/10/2014'), CDate('31/10/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('25/09/2014'), CDate('15/10/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('20/11/2014'), CDate('10/12/2014'));

INSERT INTO `periods`(`person_id`, `start_date`, `end_date`)
VALUES('1', CDate('15/11/2014'), CDate('25/11/2014'));

INSERT INTO `exclusions`(`start_date`, `end_date`)
VALUES(CDate('10/09/2014'), CDate('15/09/2014'));

INSERT INTO `exclusions`(`start_date`, `end_date`)
VALUES(CDate('01/12/2014'), CDate('20/12/2014'));

我尝试过的事情:

到目前为止，我可以使用以下查询来检测重叠时段:

So far, I am able to detect the overlapped periods using this query:

SELECT s1.person_id as person_id, 
    iif(s1.start_date <= s2.start_date, s1.start_date, s2.start_date) As start_date,
    iif(s1.end_date >= s2.end_date, s1.end_date, s2.end_date) As end_date
FROM
  periods As S1 INNER JOIN periods As S2 ON
    s1.person_id = s2.person_id And
    s1.id < s2.id And
    s2.start_date <= s1.end_date And s2.end_date >= s1.start_date

但是有一个问题，结果是:

But there is a problem as the result is:

person_id   start_date   end_date
 1          01/09/2014  15/10/2014
 1          25/09/2014  31/10/2014
 1          15/11/2014  10/12/2014

请注意，第一行和第二行也是重叠的时间段.我可以管理执行带有其自身结果的相同查询，但是感觉很奇怪.

Notice that the first and second rows are an overlapped period also. I can mange this executing the same query with its own result, but feels odd.

我需要的:

我现在遇到的其他问题是我不知道该怎么做:

The other problems I have now are that I don't know how to:

• 查找非重叠期间
• 如何用排除日期打破句段

不幸的是，我只能为此使用MsAccess，因此我无法使用谷歌搜索中发现的一些技巧，因此我在这里询问.

Unfortunately I am only able to use MsAccess for this, so I can't use some tricks that I've found googling and thus here I am asking.

推荐答案

您可以使用Calendar表(在接下来的'n'年中每天一行)解决此问题.

You could solve this with a Calendar table (one row per day for the next 'n' years).

create table calendar (
  `id` COUNTER (1,1) PRIMARY KEY,
  `calendar_date` Date
)

insert into calendar values ('2015-01-01')
insert into calendar values ('2015-01-02')
insert into calendar values ('2015-01-03')
insert into calendar values ('2015-01-04')
insert into calendar values ('2015-01-05') ... et cetera

然后:

select distinct calendar_date
from   periods p join calendar c
where  c.calendar_date between p.start_date and p.end_date and
       not exists(select * from exclusions where c.calendar_date between e.start_date and e.end_date)

这篇关于MsAccess SQL计算重叠时段中的天数，其中一些排除时段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！