Home » SQL & PL/SQL » SQL & PL/SQL » 4 CONSECTIVE ABSENT NEXT REST MARK ABSENT (ORACLE, 12C, WINDOWS 11)
| 4 CONSECTIVE ABSENT NEXT REST MARK ABSENT [message #690486] |
Wed, 20 May 2026 03:15  |
 |
hissam78
Messages: 211
Registered: August 2011
Location: PAKISTAN
|
Senior Member |
|
|
Dear Experts,
We have following scenario,
if there is any 4 days consecutive Absent in a week so weekend rest also mark as Absent, Week day start from Saturday and Restday is Friday,
any one can help pls.
sat A P P
sun A A P
mon A A A
tue A A A
wed P A A
thu P P A
fri R R R
CREATE TABLE APP_ATTENDANCE_D_1
( EMP_ID" NUMBER,
ATTENDANCE_DATE DATE,
PF VARCHAR2(20 BYTE)
)
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (10,to_date('09-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (10,to_date('10-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (10,to_date('11-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (10,to_date('12-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (10,to_date('13-MAY-26','DD-MON-RR'),'P');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (10,to_date('14-MAY-26','DD-MON-RR'),'P');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (10,to_date('15-MAY-26','DD-MON-RR'),'R');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (11,to_date('10-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (11,to_date('09-MAY-26','DD-MON-RR'),'P');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (11,to_date('11-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (11,to_date('12-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (11,to_date('13-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (11,to_date('14-MAY-26','DD-MON-RR'),'P');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (11,to_date('15-MAY-26','DD-MON-RR'),'R');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (12,to_date('09-MAY-26','DD-MON-RR'),'P');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (12,to_date('10-MAY-26','DD-MON-RR'),'P');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (12,to_date('11-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (12,to_date('12-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (12,to_date('13-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (12,to_date('14-MAY-26','DD-MON-RR'),'A');
Insert into EXPORT_TABLE (EMP_ID,ATTENDANCE_DATE,PF) values (12,to_date('15-MAY-26','DD-MON-RR'),'R');
[Updated on: Wed, 20 May 2026 03:20] Report message to a moderator |
|
|
|
| Re: 4 CONSECTIVE ABSENT NEXT REST MARK ABSENT [message #690500 is a reply to message #690486] |
Sat, 15 August 2026 12:57  |
 |
danielm
Messages: 1
Registered: August 2026
|
Junior Member |
|
|
You can handle this neatly with analytic functions by identifying consecutive runs of `A` values and then checking whether a run contains at least four working days. Once such a run is found, the Friday `R` row for that employee can be updated to `A`. I would partition by `EMP_ID` and order by `ATTENDANCE_DATE`, while treating Friday separately so the rest day does not break the consecutive-absence calculation. Also, define clearly whether the four consecutive absences must occur within the same Saturday-to-Thursday workweek, since that affects the SQL logic.
|
|
|
|
Goto Forum:
Current Time: Wed Sep 02 18:21:06 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
