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

# Distinct census with overlapping dates

Table ABC:

 Employee Department StartDate EndDate Roger Dept A 2/1/2012 2/5/2012 Roger Dept B 2/5/2012 2/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.

• ###### Re: Distinct census with overlapping dates

Maybe like this:

INPUT:

Department,

StartDate,

EndDate

FROM

(html, codepage is 1252, embedded labels, table is @1);

RESULT:

NOCONCATENATE LOAD Employee, Department, StartDate,

if(Employee=peek(Employee) and EndDate=peek(StartDate),Date(EndDate-1),EndDate) as EndDate

resident INPUT order by Employee, StartDate desc;

drop table INPUT;

It will order your table by Employee and StartDate descending, i.e. backwards in time.

Then check in every line if the Employee is identical to the previous line and if the EndDate is identical to the StartDate of the previous line (remember, previous line is forward in time). Then correct the EndDate by subtracting one day.