1 Reply Latest reply on Aug 12, 2014 12:52 PM by philmodjunk

    calculating total days between 2 dates across multiple records

    GJordan

      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 

        • 1. Re: calculating total days between 2 dates across multiple records
          philmodjunk

               I clear description of your tables and relationships would be helpful.

               Your "database of individuals" is named employee?

               Your "Location records" are in a table named What? Sites doesn't seem like quite the right table here...

               and your Permits are recorded as records in a table named permits...

               I'd think that you'd have a system similar to this:

               Sites|byPermit----<Permits>------Employee------<Locations>-----Sites|byLocation

               Employee::__pkEmployeeID = Locations::_fkEmployeeID
               Employee::__pkEmployeeID = Permits::_fkPermitID
               Sites|byLocation::__pkSiteID = Locations::_fkSiteID
               Sites|byPermit::__pkSIteID = Permits::_fkSiteID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               But I am making a lot of guesses about what you have set up here. If we can nail down the data model here, we can then look at how to track the unused days for any given permit.