calculating total days between 2 dates across multiple records
i have a Database of individuals; each individual has a unique ID.
Each individual has multiple "location records", each travel record has a field for site, arrival, and departure date
Each individual also has multiple "permit records" with a field for site (matching the available options for location in the location records), permit type, issue date, and expiration date.
I am trying (and failing) to calculate the number of days where a permit has not been utilized based on the days where the individual was not located at the site for which their permit grants access.
Individual A has a permit to enter site 1 between January 1st 2013, and June 30 2013. (181 days)
Individual A also has a permit to enter site 2 between March 1st 2013, and December 31 2014 (306 days)
They enter Site 1 on Feb 1st 2013, and depart on March 02nd 2013 (30 days spent on Site 1)
They then travel to Site 2 on March 02 2013, and depart on May 31 2013 (91 days on Site 2)
They then travel back to site 1 on June 1st 2013, and depart on June 29 2013 (+29 days on Site 1)
Of the available 181 days on the Permit for Site 1, Individual A has used 59 days, so we can reclaim the remaining 122 unused days (i.e. the days which were available on the permit prior to their first arrival, the days spent on Site 2, and the unused days following their final departure.
I cant figure out how to combine the days spent on a particular site during the validity period of their permit across a number of location records.
where "xxxxx" days can be reclaimed
P1 Start ___________________________________________________________________P1 End
xxxxxxxxxxxxxxxxxx S1enter___________________S1exit xxxxxxxxx
so far i have a long winded case calculation, which looks like below
Permits::EmpID= Employee:: EmpIDandSite ≠ Sites::SiteNameandPermits::Issue ≤ Sites::Arrival DateandPermits::Expiry > Sites::Departure Date;/*so the lesser of maximum permit duration, days on site, or the total duration of permit, less the time spend on site can be recaptured */Min ( Permits::Days Valid; Sites::Days on Site; ((Permits::Days Valid) - (Sites::Departure Date- Sites::Arrival Date));
any help or ideas would be hugely appreciated