AnsweredAssumed Answered

calculating total days between 2 dates across multiple records

Question asked by GJordan on Aug 12, 2014
Latest reply on Aug 12, 2014 by philmodjunk

Title

calculating total days between 2 dates across multiple records

Post

     Background:

     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.

      

     e.g.

     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.

      

     E..g.

     where "xxxxx" days can be reclaimed

     P1 Start  ___________________________________________________________________P1 End

     xxxxxxxxxxxxx S1enter___________S1exit

                                                             S2enter_______S2exit

                                                                            xxxxxxxxxxxxxxxxxx S1enter___________________S1exit xxxxxxxxx

      

     so far i have a long winded case calculation, which looks like below

     
          Permits::EmpID= Employee:: EmpID
     
          and
     
          Site ≠ Sites::SiteName
     
          and
     
          Permits::Issue ≤ Sites::Arrival Date
     
          and
     
          Permits::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 

Outcomes