user2478657 user2478657 -4 years ago 45
SQL Question

MS Access complex grouping and sum

I have a difficult access question I seem to be stuck on. My data is like the table below, each person has a start and end date and the time in days between the two. What I am trying to do is add together all of the Assignment Lengths that are consecutive, meaning the end date of one is the day before the start date of the next one. In my example below I want to sum the assignment length of records 1,2,3 and separately sum 4,5 because there is a break between the end and start dates. Its in access so I can either do it with a query or with vba, I'm not sure what the best solution here is.

ID Name StartDate EndDate AssignmentLength

1 -- bob -- 1/1/2013 -- 2/1/2013 -- 30

2 -- bob -- 2/2/2013 -- 3/1/2013 -- 30

3 -- bob -- 3/2/2013 -- 4/1/2013 -- 30

4 -- bob -- 5/1/2013 -- 6/1/2013 -- 30

5 -- bob -- 6/2/2013 -- 7/1/2013 -- 30

Answer Source

I added an additional record to the given data:

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

to have one period that did not span records. I named the table workperiods.

With the modified data, we can find the work period starts with:

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

We can find the work period ends with

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

Then we can build this monstrosity:

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)

Which gives:

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

which may be the desired result.

It isn't pretty, but I think it gets there.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download