Count number of days an employee missed work

Question

I have a table which stores information about employees and one of those fields is Date. I want to make a query that returns a count of the number of days they have missed, not including weekends. Date format is '2018-1-1' for example, consecutive days would be '2018-1-2', '2018-1-3', and if next record is '2018-1-5', then count would increase by 1 because 2018-1-4 was a Thursday and they should have a record for that day.

Any ideas on how to best do this?

What I have so far:

SELECT * FROM `time` where name like 'John' AND DayOfWeek(Date) not like 7 
and dayofweek(Date) not like 1  
ORDER BY `time`.`Date`  ASC

This is giving me all of the records for John excluding Saturdays and Sundays. What I want to do now is somehow find gaps between the dates that the records have for workdays. For example, consecutive days would be '2018-1-2', '2018-1-3', and if next record is '2018-1-5', then count would increase by 1 because 2018-1-4 was a Thursday

Please show the table schema in your question. `DESCRIBE tablename`. — Jim Wright, Jan 18 '18 at 15:02
Possible duplicate of [MySQL function to find the number of working days between two dates](https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates) — Jim Wright, Jan 18 '18 at 15:04
See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Jan 18 '18 at 17:03
Jim Wright is Wright by name and right by nature. But be sure to read the comments as the string provided in the accepted answer is incorrect. — Strawberry, Jan 18 '18 at 17:05
I've updated the question to have more information and what I have tried so far. — sublimesummer, Jan 18 '18 at 17:23

trincot · Accepted Answer · 2018-01-18T18:10:32.837

To make this possible you'll need a helper table, which can be useful also for many other purposes: a table with one column that has natural numbers starting from 0 up to some large n. You could create it like this:

create table nums (i int);
insert into nums values (0), (1), (2), (3);
insert into nums select i+4 from nums;
insert into nums select i+8 from nums;
insert into nums select i+16 from nums;
insert into nums select i+32 from nums;
insert into nums select i+64 from nums;
insert into nums select i+128 from nums;
insert into nums select i+256 from nums;

You can see how you double the number of records by adding a similar insert statement, but this will generate 512 records, which would be enough for your purposes.

Then you can use this query to answer your question:

SELECT ref_date
FROM   (
         SELECT date_add('2018-01-01', interval i day) ref_date
         FROM   nums
       ) calendar
WHERE  ref_date <= curdate()
AND    dayofweek(ref_date) not in (1, 7)
AND    ref_date NOT IN (
         SELECT Date
         FROM   `time`
         WHERE  name = 'John'
       )

Count number of days an employee missed work

1 Answers1