how to deal with two Interval Matches

[dijkstram]

Please some help,

We have two time periods (intervals) with a begindate and and enddate; the study enrollment period of a student and his participation during a certain period in a lesson group. These intervals can be different but have some time periods in common.

On a reference date we want to count all the students which participate in a  lesson group and  have a study enrollment on the same reference date.

For example:

  Student                  Group participation                          Enrollment

   ID                          Begindate         Endate                    Begindate         Enddate

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

2500                         8-1-2013          12-1-2013                 8-1-2012          8-1-2015

2501                        10-1-2013         12-1-2013                 8-1-2013         8-1-2015

Reference date     # Students

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

  9-1-2013                1

11-1-2013               2

How to handle this in the script with two different Intervalmatches.

Table Calendar ( Date, Month, Quarter, Year)

Table Group  with columns ( StudentID, Groupname, begindate, enddate)

Table Enrollments  with columns (StudentID, trainingname, begindate, enddate,..,..,..)

I appreciate all your suggestions

Thanks in advance,

[hic]

You should have a data model like the one in the picture below. It will take some scripting, but it is doable.

The bridge tables can for instance be created using a while loop:

[Student & Date & Group]:

Load

          StudentID & '|' & Date(begindate + IterNo() - 1) as 'Student & Date',

          StudentID & '|' & Num(begindate) & '|' & Num(enddate) as 'Student & GroupInterval'

          From Group

          While begindate + IterNo() - 1 <= enddate;

HIC

[dijkstram · Accepted solution]

Thanks Henric,

It solved my problem, very helpfull  !!