sql - MS Access 复杂分组和求和

Question

我有一个困难的 Access 问题，我似乎被困住了。我的数据如下表，每个人都有开始和结束日期以及两者之间的天数。我想要做的是将所有连续的分配长度加在一起，这意味着一个的结束日期是下一个开始日期的前一天。在下面的示例中，我想对记录 1、2、3 的分配长度求和，并分别对 4、5 求和，因为结束日期和开始日期之间有间隔。它可以 Access ，所以我可以通过查询或 vba 来完成，我不确定这里最好的解决方案是什么。
ID 名称开始日期结束日期分配长度
1 -- 鲍勃 -- 1/1/2013 -- 2/1/2013 -- 30
2 -- 鲍勃 -- 2/2/2013 -- 3/1/2013 -- 30
3 -- 鲍勃 -- 3/2/2013 -- 4/1/2013 -- 30
4 -- 鲍勃 -- 5/1/2013 -- 6/1/2013 -- 30
5 -- 鲍勃 -- 2013 年 6 月 2 日 -- 2013 年 7 月 1 日 -- 30

Answer 1

我向给定数据添加了一条附加记录:

periodID    fname   startdate   enddate
6           bob     8/1/2013    9/1/2013

有一个不跨越记录的时期。我将表命名为 workperiods。

利用修改后的数据，我们可以发现工作周期开始于:

SELECT *
FROM workperiods
WHERE periodid NOT IN
    (SELECT a.periodid
     FROM workperiods a
     INNER JOIN workperiods b ON a.startdate =b.enddate+1);

我们可以发现工作周期以

结束

SELECT *
FROM workperiods
WHERE periodid NOT IN
    ( SELECT a.periodid
     FROM workperiods a
     INNER JOIN workperiods b ON a.enddate =b.startdate-1);

然后我们可以 build 这个怪物:

SELECT startdate,
       enddate,
       enddate-startdate AS periodlength
FROM
  (SELECT startdate,
          min(enddate) AS enddate
   FROM
     (SELECT c.startdate,
             f.enddate
      FROM
        (SELECT *
         FROM workperiods
         WHERE periodid NOT IN
             (SELECT a.periodid
              FROM workperiods a
              INNER JOIN workperiods b ON a.startdate =b.enddate+1)) AS c,

        (SELECT *
         FROM workperiods
         WHERE periodid NOT IN
             (SELECT d.periodid
              FROM workperiods d
              INNER JOIN workperiods e ON d.enddate =e.startdate-1)) AS f
      WHERE f.startdate >c.enddate
        OR c.startdate=f.startdate)
   GROUP BY startdate)

给出:

startdate   enddate     periodlength
1/1/2013    4/1/2013    90
5/1/2013    7/1/2013    61
8/1/2013    9/1/2013    31

这可能是期望的结果。

它不是很漂亮，但我认为它到达了那里。