How to count “Sunday” of the month to count absent employees

I have a problem calculating employee absenteeism in a month. In my query it still counts the day of the ‘sunday’ in this month, which shouldn’t count.

This is an sample database used :

emp0003 table’s

NIK	Name
01190301	Susan

dvc0004 table’s

NIK	Enroll
01190301	2021-02-08 07:20:39
01190301	2021-02-06 14:37:08
01190301	2021-02-06 07:57:42
01190301	2021-02-05 17:29:00
01190301	2021-02-05 08:09:54
01190301	2021-02-04 19:21:38
01190301	2021-02-04 08:12:44
01190301	2021-02-03 17:44:02
01190301	2021-02-03 08:06:27
01190301	2021-02-02 18:52:15
01190301	2021-02-02 08:02:32
01190301	2021-02-01 20:07:13
01190301	2021-02-01 07:55:49
01190301	2021-01-30 16:20:56
01190301	2021-01-30 07:59:45

this is my query sql:

SELECT
    emp0003.NIK,
    emp0003.`Name`,
    DAY(CURRENT_DATE) - COUNT(
        DISTINCT
        DATE(LEFT((`dvc0004`.`Enroll`), 10)),
        (CASE
            WHEN LEFT(( `dvc0004`.`Enroll`), 10) 
                AND MONTH(LEFT((`dvc0004`.`Enroll`), 10)) = MONTH(CURRENT_DATE()) 
                AND YEAR(LEFT((`dvc0004`.`Enroll`), 10)) = YEAR(CURRENT_DATE())
            THEN 1 
        END)
    ) AS 'TOTAL ABSENT OF THE MONTH'            
FROM emp0003
LEFT JOIN dvc0004 ON emp0003.NIK = dvc0004.NIK      
WHERE emp0003.nik = '01190301' 

this is result for my query sql :

NIK	Name	TOTAL ABSENT OF THE MONTH
01190301	Susan	1

should be the result:

NIK	Name	TOTAL ABSENT OF THE MONTH
01190301	Susan	0

because so far it has only passed one ‘sunday’ this month

Answer

You can use below query.

SELECT  (total_day_of_month-Total_holiday_till_Now-Total_Valid_Attendance) AS Total_absence FROM
(SELECT SUM(CASE WHEN  DAYOFWEEK(dvc0004.Enroll)<>1 then 1 ELSE 0 END) AS Total_Valid_Attendance,
DAY(CURRENT_DATE) AS total_day_of_month,
  ROUND((
      (unix_timestamp(CURRENT_DATE) - unix_timestamp(DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)) ) /(24*60*60)
      -7+WEEKDAY(DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY))-WEEKDAY(CURRENT_DATE)
    )/7)

 + if(WEEKDAY(DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)) <= 6, 1, 0)
 + if(WEEKDAY(CURRENT_DATE) >= 6, 1, 0) AS Total_holiday_till_Now   
FROM emp0003
LEFT  JOIN dvc0004 ON emp0003.NIK = dvc0004.NIK      
WHERE emp0003.nik = '01190301' 
AND MONTH(CURRENT_DATE)=MONTH(dvc0004.Enroll) 
AND YEAR(CURRENT_DATE)=YEAR(dvc0004.Enroll) ) AS  main_query