Skip to Content
0
Former Member
Jun 09, 2009 at 06:45 PM

how to find consecutive days?

323 Views

Hi I have a requirement, where I need to find all employees who have taken 5 consecutive days off in one week.

I am trying to build a query in BI, which can identify these records, but of no avail. I am wondering if I can do this in visual Composer. My data output currently looks like this in a table.

Employee number calendar day time-off hours

100 6/8/2009 8

100 6/9/2009 8

100 6/10/2009 8

100 6/11/2009 8

100 6/12/2009 8

101 6/1/2009 8

101 6/5/2009 8

I like to loop through this table, somehow, and identify that employee 100, has 5 consecutive days off, but not 101.

I like to filter those employees who do not have 5 consecutive days off, and just show those who have 5 days off, like employee number 100. My output should look like, this

Employee number calendar day time-off hours

100 6/8/2009 8

100 6/9/2009 8

100 6/10/2009 8

100 6/11/2009 8

100 6/12/2009 8

Any ideas will be greatly appreciated. I am trying out dynamic expression, but cannot find a way to access the first row of data, while processing the second row.