Hello,
I have a table in my database that contains is a list of all the work record dates for each employee, along with a "type", which is either "ABS" (absence) or "work". For example an extract of my table looks like this:
Clock number Date Type
1 1/1/20 Work
1 2/1/20 Work
1 4/1/20 ABS
1 5/1/20 ABS
1 6/1/20 Work
1 9/1/20 Work
1 10/1/20 ABS
1 11/1/20 Work
If there isn't a record for a date then there was no working or absence on that date, i.e. it wasn't the scheduled shift pattern.
What i want to do is count how many distinct periods of absence the employee has had in a given date range. 1 absence period is classed as a period of absence without any work records in between ABS work records, no matter how many ABS days there have been in the period. i.e. if there are 2 or more consecutive days of ABS without any "Work" in between this counts as 1 absence period. In the example above, there would be 2 absence periods; 1 from 4/1/20-5/1/20, and 1 on 10/1/20.
I can't fathom out how to do this in a query, is there something obvious i am missing?
Thanks
Anna
I have a table in my database that contains is a list of all the work record dates for each employee, along with a "type", which is either "ABS" (absence) or "work". For example an extract of my table looks like this:
Clock number Date Type
1 1/1/20 Work
1 2/1/20 Work
1 4/1/20 ABS
1 5/1/20 ABS
1 6/1/20 Work
1 9/1/20 Work
1 10/1/20 ABS
1 11/1/20 Work
If there isn't a record for a date then there was no working or absence on that date, i.e. it wasn't the scheduled shift pattern.
What i want to do is count how many distinct periods of absence the employee has had in a given date range. 1 absence period is classed as a period of absence without any work records in between ABS work records, no matter how many ABS days there have been in the period. i.e. if there are 2 or more consecutive days of ABS without any "Work" in between this counts as 1 absence period. In the example above, there would be 2 absence periods; 1 from 4/1/20-5/1/20, and 1 on 10/1/20.
I can't fathom out how to do this in a query, is there something obvious i am missing?
Thanks
Anna