How to find missing dates in a DATE column

Question

I have this kind of date table:

I want to find/detect all blank dates between them, or showing how many days it skipped.

I tried:

SELECT date 
FROM tbl_attendance 
WHERE date < '2022-05-01' 
AND date >= '2022-04-01'
AND employee_id = '4'

But it won't show the blank dates between them. Any help would be appreciated

There are many similiar questions already, please check them first: https://stackoverflow.com/search?q=mysql+find+missing+dates — Kristian, Apr 22 '22 at 09:21
Does this answer your question? [How to find missing data rows using SQL?](https://stackoverflow.com/questions/1852293/how-to-find-missing-data-rows-using-sql) — Luuk, Apr 22 '22 at 09:24
A [mcve] is a great start when asking for SQL assistance. I.e. provide sample data for all related columns (date, employee_id), and also _specify_ the expected result - and use properly formatted text, not images. — jarlh, Apr 22 '22 at 09:33

score 0 · Answer 1 · answered Apr 22 '22 at 09:23

0

It looks like you are trying to find missing values? I think your question needs more clarity, do you have an example of what you are expecting the outcome to be? The following will return any rows where the data is blank or null:

    SELECT date 
    FROM tbl_attendence
    WHERE date IS NULL

Your select statement will return any rows where the date is in that range and the employee ID is 4.

answered Apr 22 '22 at 09:23

AradiaJoanne

28
4

2

"I think your question needs more clarity" When you think so, you should not post an answer. – Luuk Apr 22 '22 at 09:25
Welcome to SO AradiaJoanne and thank you for contributing! Adding to @Luuk's comment, posting a comment would be better when clarification is needed, which also avoids possible downvotes :) – Kristian Apr 22 '22 at 09:43

FanoFN · Answer 2 · 2022-04-23T01:35:40.433

I'm assuming you want all the dates between the first and last date return even if it doesn't exists in the table. There are a few ways available depending on your MySQL version but since older versions provide more steps (and most have already migrated to newer version), I'll post a suggestion for newer MySQL (or MariaDB) version that supports window function.

If you are on latest MySQL version or at least v8+ (OR if you are on MariaDB v10.2 and above), you can use common table expression function to custom generate the date range based on the data you have:

WITH RECURSIVE cte AS (
 SELECT employee_id, DATE_FORMAT(MIN(date), '%Y-%m-01') AS dt, 
        MIN(date) AS mndt FROM tbl_attendance 
     GROUP BY employee_id
   UNION ALL
 SELECT employee_id, dt+INTERVAL 1 DAY, mndt FROM cte 
   WHERE dt+INTERVAL 1 DAY <= LAST_DAY(mndt))
SELECT cte.employee_id,
       cte.dt,
       t.date
 FROM cte 
 LEFT JOIN tbl_attendance t
 ON cte.dt=t.date

Here's a demo

For older versions, you may want to consider creating a calendar table using methods posted in this question count saturdays before 15 date as working days and skip others

How to find missing dates in a DATE column

2 Answers2