SQL Question:How to Calculate Weekly Employee Adherence Status for Return-to-Office Tracking

 In This post we will delve into How to Calculate Weekly Employee Adherence Status for Return-to-Office Tracking with scenario and Execution.

XYZ Company has announced that all employees are required to return to the office, following the rules outlined below:

1. Employees associated with the Hybrid Model must work from the office for a minimum of six hours per day, at least two days a week.  

2. Failure to adhere to this requirement will result in a deduction of 0.25 leave days for each occurrence, with a maximum deduction of two leave days per month.

here is the sample test data for above scenario.

if you want more on only scenarios, please watch my video to get more details Youtube video

Here is the Oracle SQL Query

-- LOGIN STATS

WITH login_stats AS 

(SELECT EMPNO,COUNT(WORK_DATE) AS WORKING_DAYS,

SUM(CASE WHEN LOGIN_HRS>=6 THEN 0 ELSE 1 END ) NOT_WORKING_DAYS

FROM EMPATT GROUP BY EMPNO)

--SELECT * FROM LOGIN_STATS;

 

-- DETAILS LOGIN WITH ADHRENCE LOGIC

,

LOGIN_DETAILS AS 

(

SELECT 

E.EMPNO,E.WORK_DATE,E.LOGIN_HRS,S.WORKING_DAYS,S.NOT_WORKING_DAYS,

 

CASE WHEN E.LOGIN_HRS>=6 AND S.WORKING_DAYS>=2 THEN 'ADHERE' ELSE 'NO ADHERE' END AS ADHERE_STATUS,

 

CASE 

  WHEN E.LOGIN_HRS>=6 AND S.WORKING_DAYS>=2 THEN ''

  WHEN E.LOGIN_HRS<6 AND S.WORKING_DAYS=2 AND S.NOT_WORKING_DAYS=2 THEN 'LESS THAN 6HR'   

  WHEN E.LOGIN_HRS<6 AND S.WORKING_DAYS=2 AND S.NOT_WORKING_DAYS=1 THEN 'LESS THAN 6HR'

  WHEN E.LOGIN_HRS>=6 AND S.WORKING_DAYS=1 AND S.NOT_WORKING_DAYS=0 THEN 'LESS THAN 2 DAYS'  

  WHEN E.LOGIN_HRS<6 AND S.WORKING_DAYS=1 AND S.NOT_WORKING_DAYS=1 THEN 'LESS THAN 6HR' 

END AS ADHERE_REASON,

 

CASE 

  WHEN E.LOGIN_HRS>=6 AND S.WORKING_DAYS>=2 THEN 0

  WHEN E.LOGIN_HRS<6 AND S.WORKING_DAYS=2 AND S.NOT_WORKING_DAYS=2 THEN 0.5   

  WHEN E.LOGIN_HRS<6 AND S.WORKING_DAYS=2 AND S.NOT_WORKING_DAYS=1 THEN 0.25

  WHEN E.LOGIN_HRS>=6 AND S.WORKING_DAYS=1 AND S.NOT_WORKING_DAYS=0 THEN 0.25  

  WHEN E.LOGIN_HRS<6 AND S.WORKING_DAYS=1 AND S.NOT_WORKING_DAYS=1 THEN 0.5

END AS LEAVE_DEDUCTED

 

FROM EMPATT E JOIN LOGIN_STATS S ON E.EMPNO=S.EMPNO

)

--SELECT * FROM LOGIN_DETAILS ORDER BY 1

 

-- aggregated data

,adhere_summary as 

(

 SELECT EMPNO, MAX(NOT_WORKING_DAYS) AS NOT_WORKING_DAYS,

 MAX(ADHERE_STATUS) AS ADHERE_STATUS,

 MAX(ADHERE_REASON) AS ADHERE_REASON,

 MAX(LEAVE_DEDUCTED)AS LEAVE_DEDUCTED

 FROM LOGIN_DETAILS

 GROUP BY EMPNO

) 

n  Derived absent employee details

, ABSENT AS

(

SELECT ID,

  CASE WHEN UPPER(WORKTYPE)<>'HYBRID' THEN 0 ELSE 2 END AS NOT_WORKING_DAYS,

  CASE WHEN UPPER(WORKTYPE)<>'HYBRID' THEN 'EXCLUDE' ELSE 'NOT ADHERE' END AS ADHERE_STATUS,

 CASE WHEN UPPER(WORKTYPE)<>'HYBRID' THEN 'EXCLUDE' ELSE 'LESS THAN 2' END AS ADHERE_REASON,

  CASE WHEN UPPER(WORKTYPE)<>'HYBRID' THEN 0 ELSE 0.5 END AS LEAVE_DEDUCTED

  FROM EMPLOYEE 

  WHERE NOT EXISTS(SELECT 1 FROM EMPATT L WHERE L.EMPNO= EMPLOYEE.ID)

  )

  --SELECT * FROM adhere_summary ORDER BY 1

  ,

  FINAL_DATA AS(

  SELECT * FROM adhere_summary UNION ALL 

  SELECT * FROM ABSENT

  )

--SELECT * FROM FINAL_DATA ORDER BY 1;

  

  SELECT

  E.ID,E.ENAME,E.WORKTYPE,E.LOCATION,F.NOT_WORKING_DAYS,F.ADHERE_STATUS,F.ADHERE_REASON,F.LEAVE_DEDUCTED

  FROM EMPLOYEE E LEFT JOIN FINAL_DATA F ON E.ID=F.EMPNO

  ORDER BY E.ID