How to find missing dates in a DATE column-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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