Count instances of consecutive dates for associated name (VBA, SQL)

Question

Good morning all,

I am trying to determine instances of consecutive dates (excluding Sunday) from a data set. The data is stored in Access and I am pulling the required dates into Excel. I am then trying to determine how many instances each person has in the data provided. Example below.

Data example:

| Name | Date of absence|
| Bob  | 02/01/17       | 
| Jill | 02/01/17       |
| Bob  | 03/01/17       |
| Jill | 04/01/17       |

Result example:

Bob - 1 Instance, 2 days
Jill - 2 Instance, 2 days

I started trying to work through this with VBA in Excel using loops to rotate through each instance of absence until all people had been completed/ticked off, however the code was becoming really cumbersome and it felt very inefficient, not to mention how slow it was getting for larger data sets! I wonder if it is possible to query the database for the info or to write something a bit more efficient.

Any help or suggestions would be appreciated!

Update:

Testing Tom's suggestion;
Sql = "SELECT Absence.Racf,count(RecordDate) as dups" Sql = Sql & " FROM Absence" Sql = Sql & " left outer join" Sql = Sql & " (select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as date1 from Absence) t1" Sql = Sql & " on Absence.RecordDate=t1.date1 and Absence.Racf=t1.Racf" Sql = Sql & " where date1 Is Not Null" Sql = Sql & " group by Absence.Racf"

But unfortunately on the list of dates below it returns 7, instead of 5.

Dates: 23-Feb-16,24-Feb-16,08-Aug-16,09-Aug-16,10-Aug-16,31-Aug-16,24-Oct-16,25-Oct-16,26-Oct-16,25-Jan-17,26-Jan-17,27-Jan-17

Answer 1

So this is how the SQL might actually look in an Access query

SELECT table1.name,count(date) as dups
FROM Table1
left outer join
(select name, [date]+IIf(Weekday([Date],7)=1,2,1) as date1 from table1) t1
on table1.date=t1.date1 and table1.name=t1.name    
where date1 is not null
group by table1.name
;

If you want to run this from Excel using a macro, here is a useful reference.

I lifted the code from there and changed the lines which set up the SQL query string to

SQL = "SELECT table1.name,count(date) as dups"
SQL = SQL & " FROM table1"
SQL = SQL & " left outer join"
SQL = SQL & " (select name, [date]+IIf(Weekday([Date],7)=1,2,1) as date1 from table1) t1"
SQL = SQL & " on table1.date=t1.date1 and table1.name=t1.name"
SQL = SQL & " where date1 Is Not Null"
SQL = SQL & " group by table1.name"

and it worked fine.

Try this if you want to get sequences with length greater than one

SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate
FROM (Absence LEFT JOIN (select Racf, RecordDate+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate1 from Absence) AS t1 ON (Absence.RecordDate = t1.RecordDate1) AND (Absence.Racf = t1.Racf))
LEFT JOIN (select Racf, [RecordDate]-IIf(Weekday([RecordDate],2)=1,2,1) as RecordDate2 from Absence) AS t2 ON (Absence.RecordDate = t2.RecordDate2) AND (Absence.Racf = t2.Racf)
WHERE (((t1.RecordDate1) Is Not Null) AND ((t2.RecordDate2) Is Null))
GROUP BY Absence.Racf;

Or this if you want to get sequences of one or more consecutive dates

SELECT Absence.Racf, Count(Absence.RecordDate) AS CountOfRecordDate
FROM Absence LEFT JOIN (select Racf, [RecordDate]+IIf(Weekday([RecordDate],7)=1,2,1) as RecordDate2 from Absence) AS t2 ON (Absence.RecordDate = t2.RecordDate2) AND (Absence.Racf = t2.Racf)
WHERE (((t2.RecordDate2) Is Null))
GROUP BY Absence.Racf;

adding to the SQL string as before.

Answer 2

A SQL approach would be something along the lines of, based on a table 000Absence, which is the data from examples EEName and AbsDate.

SELECT abs1.EEName, abs1.AbsDate, 
(select count(abs2.EEName) from 000Absence as abs2 where abs2.[EEName]=abs1.[EEName]) AS INSTANCES, 
(select count(abs3.EEName) from 000Absence as abs3 where abs3.[EEName]=abs1.[EEName] and abs3.[AbsDate]=abs1.[AbsDate]+iif(weekday(abs3.[AbsDate],7)=1,2,1)) AS CONSECUTIVE
FROM 000Absence AS abs1;

Where the output can be got from the query, grouping by Employee etc.

Answer 3

This can be done using array formula in Excel. In D I have =INDEX($A2:$A$15,MATCH(0,COUNTIF($D$1:$D1,$A2:$A$15),0)) to get the unique employees, then in E I have the following to count the instances =SUM(--(($A$1:$A$15=D1)*(OFFSET($A$1:$A$15,1,0)=D1)*(OFFSET($B$1:$B$15,1,0)-$B$1:$B$15)=1)) which gives the result something like this. You'll need to add another criteria, based on weekday (I will adjust a little later as running low on time) This relies on the data being in date order

EDIT : I understand this is not the full answer and will require modification, a starting point :o)

Covering the Sunday absence (will still need weekday check):

=D1 & " " & COUNTIF($A$1:$A$15,D1) &" instances " & SUM(--(--($A$1:$A$15=D1)*--(OFFSET($A$1:$A$15,1,0)=D1))*--(--(OFFSET($B$1:$B$15,1,0)-$B$1:$B$15=1)+--(OFFSET($B$1:$B$15,1,0)-$B$1:$B$15=2)))&" Consecutive"

Checking the weekday also

=D2 & " " & COUNTIF($A$1:$A$15,D2) &" instances " & SUM(--(--($A$1:$A$15=D2)*--(OFFSET($A$1:$A$15,1,0)=D2))*--(--(OFFSET($B$1:$B$15,1,0)-$B$1:$B$15=1)+--(WEEKDAY(OFFSET($B$1:$B$15,1,0),2)=1)*((OFFSET($B$1:$B$15,1,0)-$B$1:$B$15=2)))) & " Consecutive"