CODEWITHSUNDEEP
phpmysqlsqldatabase
reja eja
reja eja

Reputation: 93

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

Upvotes: 2

Views: 190

Answers (2)

Ahmmed
Ahmmed

Reputation: 1048

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

Upvotes: 1

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

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

Upvotes: 1

Related Questions