2 Replies Latest reply: Apr 26, 2012 1:44 PM by tommyhot RSS

    Distinct census with overlapping dates

      Table ABC:

      EmployeeDepartmentStartDateEndDate
      RogerDept A2/1/20122/5/2012
      RogerDept B2/5/20122/20/2012

       

      I'm looking to get the census by Department. The problem I have is the start date of the first row is overlapping the end date of the first row. So when using the intervalmatch() function to tie the dates to the employee I get following:

       

                     2/1/2012................2/5/2012..............2/20/2012

      Dept A     Roger     Roger      Roger

      Dept B                                 Roger                    Roger

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

      Total           1          1               2                         1

       

      The census total should always be 1/day.

      Dept A census should be 1 from 2/1/2012 to 2/4/2012 and Dept B census is 1 from 2/5 to 2/20.

       

      Does anyone know how to handle this? Any help is appreciated.