-1

Here is my ticks (timeclock) table:

_id_
#employee_id  
created_at

This table stores, for each employee, the timestamp they tick. Sometimes some of them tick multiple times a day, some only once or none at all when they didn't come to work.

I have a query which allows me to know exactly how many times an employee wasn't at work during the last 7 days, but I would like to have in the results the days they didn't work. Which means, every employee who worked less than 6 days in the last 7 days, and which dates are missing. And I have no clue.

Here is how I query who was absent more than once during the last 7 days, and how many times:

SELECT `id`, `employee_id`, COUNT(`date`)-6 missing
FROM (
    SELECT `id`, `employee_id`, DATE(created_at) date
    FROM `ticks`
    WHERE `created_at` >= '2017-09-16' AND `created_at` < '2017-09-23'
    GROUP BY `employee_id`, `date`
) ticks
GROUP BY `employee_id`
HAVING missing < 0

Based on that or another query, I would like to know which date is missing in that range (by the way, I can get the range from another query, there is always at least 1 tick per day, even on Sunday).

Here is an sqlfiddle with an example dataset. The results I'm expecting, would be:

+-------------+--------------+
| employee_id | date_missing |
+-------------+--------------+
|           2 |   2017-09-18 |
|           2 |   2017-09-19 |
|           3 |   2017-09-20 |
+-------------+--------------+
  • You can create a table with the dates you are checking for and left join that to your table of tick dates to find the missing dates. If you want the dates to be autogenerated in mysql there is a hack that's a little odd but functional to mimic generate_series. stackoverflow.com/questions/6870499/… – indiri Sep 23 '17 at 15:33
  • @indiri Thanks, but how would I get the missing dates for each employee? A left join will just let me know that dates are missing but not the details, isn't it ? – Max13 Sep 23 '17 at 15:39
  • 1
    Something like:SELECT nd.* FROM newDatesTable nd LEFT JOIN myTicks t on nd.date=t.date WHERE t.date is NULL – indiri Sep 23 '17 at 15:48
  • @indiri Here is, from what I understand, your suggestion: sqlfiddle.com/#!9/98c71/1 – Max13 Sep 23 '17 at 18:13
  • I messed with the fiddle. Your answer is below. – indiri Sep 23 '17 at 19:19
5

The first part of this will generate the dates you are checking for.
The second grabs all employees who logged in during that week. If you have a separate employee table you can substitute that here so you will get employees who didn't log in at all the whole week. The last part it joins to is the ticks you have.

LIMIT 7 gives you a week. Make this number bigger if you want more days at once.

WHERE ticks.date is null will pick only days where ticks doesn't have a record.

SELECT `dates`.`date`, `employees`.`employee_id`
FROM (
    SELECT 
    date_format(adddate('2017-09-16', @num:=@num+1), 
        '%Y-%m-%d') date
    FROM `ticks`, (select @num:=-1) num
    LIMIT 7
) dates 
CROSS JOIN (
    SELECT DISTINCT `employee_id`
    FROM `ticks`
) employees
LEFT JOIN (
    SELECT `id`, `employee_id`, DATE(created_at) date
    FROM `ticks`
) ticks 
    on dates.date = ticks.date
        and ticks.`employee_id` = employees.`employee_id`
WHERE ticks.date is null
  • Replacing this query's employees derived table with an actual employee reference table is a really good suggestion. But I would go further and also suggest replacing dates with a calendar table. – Andriy M Sep 24 '17 at 9:42
  • Perhaps. The benefit to the dates table is that it can also be used to generate_series for an integer instead of dates. The only real requirement is that the table that it selects from needs to have enough available rows. If MySql ever adds the option for generate_series (cross fingers) then this will no longer be needed. – indiri Sep 24 '17 at 15:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.