How to count "Sunday" of the month to count absent employees

Question

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

Please include a db fiddle demo (if you want high quality answers without a long wait time). Is this SQLServer or MySQL? — mickmackusa, Feb 08 '21 at 04:34
Sample data (as DDL+DML) and expected results are a must for these questions. — Dale K, Feb 08 '21 at 05:09
I've added a sample db, my query results and the results I want.. @mickmackusa — reja eja, Feb 08 '21 at 07:17
@DaleK : I've added a sample db, my query results and the results I want. — reja eja, Feb 08 '21 at 07:18
Where is the db fiddle demo? I volunteer predominantly from my mobile, so I seldom take the time to create a schema to solve someone else's problem. With a db fiddle, your question becomes more attractive to answer. — mickmackusa, Feb 08 '21 at 08:59

score 1 · Answer 1 · answered Feb 08 '21 at 05:54

If I understood you correctly you want to calculate any employees absence in working days (Monday-Saturday). To achieve that you need to know number of working days in a given date range and presence of any employee on those days.

You can calculate easily the presence of an employee between Monday to Saturday through below line of code (considering that LEFT ( ( dvc0004.Enroll ), 10 ) represents a valid date)

(CASE WHEN weekday(LEFT ( ( `dvc0004`.`Enroll` ), 10 ) )>0 then 1 else 0 end) AS 'TOTAL ABSENT OF THE MONTH'

But just subtracting the count from current_date() won't do the work. You also need to consider number of Sundays in first day of current month and current date. Here I am sharing a link to achieve that:

MySQL function to find the number of working days between two dates

score 1 · Accepted Answer · answered Feb 08 '21 at 11:48

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

Try this / code-only answers are low value on Stack Overflow because they miss an opportunity to educate/empower the OP and thousands of future researchers. — mickmackusa, Feb 08 '21 at 20:33

How to count "Sunday" of the month to count absent employees

2 Answers2