3 Replies Latest reply on Aug 31, 2011 1:55 PM by philmodjunk

    Calculation based on 2 date fields

    caskew

      Title

      Calculation based on 2 date fields

      Post

      I making 2 tables.

      One will have all of our available school dates.  Each date will have it's on unique date id.

      From another table I will have:

       Student name

      level1 start date, total number of days on level1

      level2 start date, total number of days on level2

      Total days in Program

      ----------------------------------------------------------------------

      What is the best way to get the (total number of days on level1/level2) based on the dates table?

      If they have not started level2, I would like for it to count the number of available dates between the (level1 start date) and todays date, based on available dates in the dates table .

      If they have started level2, I would like for it to count the number of available dates between the (level2 start date) and todays date, based on available dates in the dates table .

      And for total days in program, I would like to just add the 2 total number of days fields

        • 1. Re: Calculation based on 2 date fields
          philmodjunk

          Whenever you start adding multiple copies of fields to a table, you need to consider whether a related table will be a better option. The Level, start date, total number of days part of your description suggest such a possibility. If there is any chance that you either have more than 2 levels or that more such levels might be added in the future, I strongly recommend you consider that change.

          Sticking with your current setup, you can use relationships with inequality operators to match to a range of dates in your SchoolDates table. The count of these records will then give you your total days.

          First, we need a field that returns either today's date or the date that they left level1 for level2: This must be a field of type calculation, unstored with Date as it's return type:

          cDateLeft1 : If ( Level2StartDate ; Level2StartDate ; Get ( currentDate ) )

          and we'll use cToday : Get ( currentDate ) as well. This field must also have the same properties for type, return type and storage.

          Then you can make two occurrences of SchoolDays, Level1Schooldays, Level2Schooldays and link them to your Student table like this:

          Student::Level1StartDate < Level1Schooldays::date AND
          Student::cDateLeft > Level1Schooldays::date

          Student::Level2StartDate < Level2Schooldays::date AND
          Student::cToday > Level2Schooldays::date

          To compute the number of days you can use one of two options: Calculation fields in Student, can use Count ( Level1Schooldays::date ) and Count ( Level2Schooldays::date ) to compute the days in each level or you can define a summary field in School days to compute the "count of" date. If you place this summary field, taken from level1Schooldays on your student layout, it will report the number of school days for level 1. Add it from Level2SchooldDays, and this same summary field will compute the total number of days in level 2.

          • 2. Re: Calculation based on 2 date fields
            caskew

            Here is a better explanation of what I am trying to do........ How do I get this to work?

             

            Table1 "Schooldates" (these are the available dates that should be used to count level days)

            Fields

            DateID - unique dateid

            Date

            --------------------------------------------------------------------------------------------------------------------------------------------      

            Table2 "Students" (needs to use days from "Schooldates" table to get amounts)

            Fields

            StudentID         - unique studentid

            Student            - student name

            Level1startdate  - level1start date  

            level1daycount   - count of days or available days from the schooldates table that fall between the range of the level1startdate and current available             schooldate

            level2startdate   - level2start date 

            level2daycount   - count of days or available days from the schooldates table that fall between the range of the level2startdate and current available             schooldate

            totaldays           - level1daycount+level2daycount  

            -------------------------------------------------------------------------------------------------------------------------------------------- 

            example

            school dates......8/1/11, 8/2/11, 8/3/11, 8/4/11, 8/5/11 for available dates

             

            student was on level1 8/1/11, 8/2/11, 8/3/11

            level1daycount should equal 3

             

            student was on level2 8/4/11, 8/5/11

            level2daycount should equal 2

             

            totaldays should equal 5

            • 3. Re: Calculation based on 2 date fields
              philmodjunk

              That's exactly what I understood you to have. What I suggested should work with that.

              If "table occurrence" is a new concept, you might want to read this thread:  Tutorial: What are Table Occurrences?