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
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)
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.