Calculating consecutive months on absence dates

[Dawnie]

Hello all. I wondered if someone may be able to help me with something I am struggling to do. I have a table to data containing absence dates. I need to be able to work out the people with absences in 4 or more consecutive months. Some absences will span over more than one month so need to be able to calculate based on start and end dates. Each absence is on a different line.

ID Start Date. End Date
1111. 01/01/2019. 12/01/2019
1111. 15/02/2019. 15/02/2019
1111. 20/03/2019. 06/04/2019. (4 consecutive months)
2222. 01/01/2018. 01/01/2018
2222. 09/02/2018. 09/02/2018
2222. 23/03/2018. 30/03/2018
2222. 07/04/2018. 10/04/2018 ( 4 consecutive months)
3333. 10/07/2017. 10/07/2017
3333. 25/08/2017. 26/08/2017
3333. 01/10/2017. 01/10/2017
3333. 04/11/2017. 08/11/2017 ( not consecutive months)

I also need it to be able identify if it is consecutive but across years so if someone is off Nov, Dec, Jan and Feb it counts it as consecutive.

any help would be much appreciated.
dawn

 

[CJ_London]

think you need to include some more data so we can get a better idea of the scope of the requirement.

For example ID 1111 - if there is another entry for say 07/05/2019 to 09/05/2019 would thae be included as a second 4 consecutive months? or do you start again from 7th April?

As described, the month does not start from the 'first' start date, but the first of the month of that start date and ends on the 'last' day of the month of the end date in the last record.

ie. if the first record of user id 1111 had a start date of say 10th Jan, and a last date in the last record of 6th April, that is less than 3 months, but you still want it included as 4 consecutive months?

And what is the output required - just to identify the ID's where there are 4 consecutive months? or something more? So please also include an example of the required output from your supplied example data.

 

[linxpatrick]

Break the absences apart by year, month. and the number of days absent per block of days absent.

1111 01/01/1019 to 12/01/2019 would be broken apart into 2019 for the year, 1 for the month and 11 for the days. If the start of the period you are testing begins on 11/01/2019 you will have to calculate the difference between 01/01/2019 and 10/01/2019 and subtract that from the total for the month.
1111 15/02/2019 to 15/02/2019 would be broken apart into 2019 for the year, 2 for the month and 1 for the days.

Store this data in a work table. For cases that include multiple months add additional records that represent the entire month for each month the user is absent. An absence from 20/03/2019 to 06/04/2019 would be broken down to 20/03/2019 to 31/03/2019, 01/04/2019 to 06/04/2019.

With that data you can calculate the number of days for each month the person was absent using the DateDiff() function. If there's a month with two non-consecutive day(s) of absences you would have multiple records for the same month and would need to sum the count of days in those records to get the total for the month.

If your period doesn't start on even month boundaries you will need to consider this when initially querying the data. For example, if the period for reporting starts on 11/01/2019 you will need to calculate the number of days that falls between 01/01/2019 and 10/01/2019 and remove that from the number of days in the entire block.

Once the data is split apart into individual blocks of days absent you can write a query that sums the days absent for all the periods that fall in your date range and subtract out any days from a range that fall outside of the start and end dates.

 

[arnelgp]

see Query1.

 

[Dawnie]

think you need to include some more data so we can get a better idea of the scope of the requirement.

For example ID 1111 - if there is another entry for say 07/05/2019 to 09/05/2019 would thae be included as a second 4 consecutive months? or do you start again from 7th April?

As described, the month does not start from the 'first' start date, but the first of the month of that start date and ends on the 'last' day of the month of the end date in the last record.

ie. if the first record of user id 1111 had a start date of say 10th Jan, and a last date in the last record of 6th April, that is less than 3 months, but you still want it included as 4 consecutive months?

And what is the output required - just to identify the ID's where there are 4 consecutive months? or something more? So please also include an example of the required output from your supplied example data.

Hi.

To answer your questions:
1 - If there was another absence in May this would just need to be included to be 5 consecutive months. We require everyone with 4 or more consecutive months so there is no need to split into two separate 4 month blocks.

2 - The months just need to start from 1st of each month. We do not need to start the 4 months on the actual date of the first absence. For your example above. As there was an absence in Jan, Feb, Mar and April this would be counted as 4 months regardless of the fact it started and ended mid months.

3 - Output. I just need to be able to identify which ID's have been off 4 or more months in a row. I do not need anymore info.

Ideally I would like to see something along the lines of below so I can then filter on the last column for those containing 4 or higher. (THis would then allow me to still see all the separate dates of that ID's absence.

ID	Start Date	End Date	Consecutive Month Absent	Max Consecutive Absence for ID
1111	01-Jan-19	12-Jan-19	1	4​
1111	15-Feb-19	15-Feb-19	2	4​
1111	20-Mar-19	06-Apr-19	4	4​
2222	01-Jan-18	01-Jan-18	1	4​
2222	09-Feb-18	09-Feb-18	2	4​
2222	23-Mar-18	30-Mar-18	3	4​
2222	07-Apr-18	10-Apr-18	4	4​
3333	10-Jul-17	10-Jul-17	1	2​
3333	25-Aug-17	26-Aug-17	2	2​
3333	01-Oct-17	01-Oct-17	1	2​
3333	04-Nov-17	08-Nov-17	2	2​
4444	01-Jan-18	01-Jan-18	1	5​
4444	09-Feb-16	09-Feb-16	2	5​
4444	23-Mar-16	30-Mar-16	3	5​
4444	01-Oct-17	01-Oct-17	1	5​
4444	04-Nov-17	08-Nov-17	2	5​
4444	12-Dec-17	14-Jan-18	4	5​
4444	02-Feb-18	02-Feb-18	5	5​

 

[Dawnie]

see Query1.

Hello,

Thank you for the reply. Your database is showing the absence as cumulative and therefore showing ID 3333 as having 4 months. I require it to count the consecutive months and therefore 3 should only have a max of 2.

 

[arnelgp]

here i am using a Function (see Module1) on the Query.

 

[linxpatrick]

I took a look at Module1. Within the function fncCummuAbsent, there are no comments to explain what you want to happen. For example, what is ayDI?

You can safely use up to 32 characters in a function name so why not expand fncCummuAbsent to it's full name. Not sure what Cummu means but I suspect it means cumulative?

From what I see, you are trying to count days absent by counting the number of days in each date range specified within each record for the same individual for a specified date range.

Storing the absense dates/ranges is fine the way you did it but counting the actual number of days absent seems to be the issue. For the purposes of reporting and analysis I still recommend converting those date ranges into more records that you can easily count using a single query. While I was more focused on breaking apart each month it may make more sense to create one record per day per absence. Each record would only have to contain the date of the absence. You could then perform a SELECT COUNT(*) to count the number of records within the desired date range to return the total number of absences.

If you are actually trying to count absences for a block of days as a single absence then I still recommend you create a separate set of records that represents each absence or a block of consecutive days or absences as one absence and then use a SELECT COUNT(*) to count the records that fall within the date range.

 

[Dawnie]

Hello linxpatrick.

I am not trying to calculate lengths of absences, I am trying to calculate how many consecutive months an employee has had time off. The module that /arnelgp has kindly provided does do this accurately. The next step I require however is to be able to work out the max cumulative number against each employee ID. (I.e column four of my above example). If I can add this column it then allows me to be able to filter the ID’s for people with more than 4 against them whilst retaining all the rows containing the absence details.

 

[CJ_London]

bit confused about what you are trying to achieve - seems to be a half step to get to data which you then still need to filter. When what you say you want is

3 - Output. I just need to be able to identify which ID's have been off 4 or more months in a row. I do not need anymore info.

this query achieves that

first create a union query which summarises the data in a form that is easier to analyse. change the name of the table to suit and save as ASQry

SELECT ID, [start date]-day([start date])+1 as Adate
FROM absence
UNION SELECT ID,  [end date]-day([end date])+1
FROM absence;

then copy/paste this code to a new query and run it

SELECT ASQry.ID, ASQry.adate, Count(ASQry_1.adate) AS CountOfadate
FROM ASQry INNER JOIN ASQry AS ASQry_1 ON ASQry.ID = ASQry_1.ID
WHERE (((ASQry_1.adate) Between DateAdd("m",-3,[asqry].[adate]) And [asqry].[adate]))
GROUP BY ASQry.ID, ASQry.adate
HAVING (((Count(ASQry_1.adate))=4))

which from your example data produces this result

Query32 Query32

ID	adate	CountOfadate
1111	01/04/2019​	4​
2222	01/04/2018​	4​
4444	01/01/2018​	4​
4444	01/02/2018​	4​

from your requirement, you would only need to return the ID's, so you could use this instead

SELECT DISTINCT ASQry.ID
FROM ASQry INNER JOIN ASQry AS ASQry_1 ON ASQry.ID = ASQry_1.ID
WHERE (((ASQry_1.adate) Between DateAdd("m",-3,[asqry].[adate]) And [asqry].[adate]))
GROUP BY ASQry.ID, ASQry.adate
HAVING (((Count(ASQry_1.adate))=4))

 

[Dawnie]

Morning CJ
Apologies but I think I may be confusing matters. The reason I wanted the results to look like my table is because I want to be able to filter in the final column so I can keep sight of the absence dates that have made the employee hit the trigger. If I input the query as you have suggested I find out who the employees are but I lose sight of the actual absence data.

 

[arnelgp]

see query2.

 

[Dawnie]

Hi, Thanks for the above. I seem to be getting an error message on the 'Data type mismatch in criteria expression' which i believe is on the date format section of the code. My dates are in UK format of dd/mm/yyyy. I have tried changing the format to this however it still doesn't work. Am I missing something obvious? I am querying against a linked excel sheet and not a table so not sure if that makes a difference.

 

[arnelgp]

whether you are using UK or EN, on vba it's either EN or yyyy-mm-dd, when comparing dates.
is the field a Real Date datatype?

 

[Dawnie]

This is the formatting on the spreadsheet. Would access automatically use a different format?

 

[arnelgp]

are we talking Excel here?

in vba, whatever the Format of your date field, you use
#mm/dd/yyyy# format in vba.

format is just for Display purpose, it does not alter
the internal representation of the date datatype.

EDIT:
go to the worksheet.
inspect the Date field. are there any blanks on either start and end date?
maybe there are "text" input that look like a Date?

 

[Dawnie]

The table in access is linked to an excel spreadsheet however I'm building the calculations in access. I have checked the date format and all is set to date, there is also no blanks in my date columns but I'm still getting the error.

 

[arnelgp]

can you post a sample xl workbook?

 

[Dawnie]

I have been trying to use on the attached. Columns D and E are the columns I am trying to get access to replicate with queries.

 

[arnelgp]

you Relink Sheet1 to your local copy of Absence Example Data.xlsx