SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Request for sql query to exclude weekends, leave for attendances

Afzal BaigNov 14 2021 — edited Nov 14 2021

CREATE TABLE APPS.XX_ATTENDANCE_MAIN
(
 PERSONID   NUMBER,
 TRANS_DATE  DATE,
 IN_TIME    VARCHAR2(240),
 OUT_TIME   VARCHAR2(240)
)

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('01-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('02-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('03-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 1,TO_DATE('04-11-2021','DD-MM-YYYY'),'07:30','14:30');

INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 2,TO_DATE('01-11-2021','DD-MM-YYYY'),'07:30','14:30');


INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 2,TO_DATE('02-11-2021','DD-MM-YYYY'),'07:30','14:30');


INSERT INTO XX_ATTENDANCE_MAIN
VALUES( 2,TO_DATE('03-11-2021','DD-MM-YYYY'),'07:30','14:30');

CREATE TABLE XX_ABSENCE
(PERSONID NUMBER,
DATE_START DATE,
DATE_END DATE);

INSERT INTO XX_ABSENCE
VALUES(1, TO_DATE('07-11-2021','DD-MM-YYYY'),TO_DATE('09-11-2021','DD-MM-YYYY'));
INSERT INTO XX_ABSENCE
VALUES(2, TO_DATE('07-11-2021','DD-MM-YYYY'),TO_DATE('09-11-2021','DD-MM-YYYY'));


CREATE TABLE XX_HOLIDAYS
(TYPE VARCHAR2(240),
DATE_START DATE,
DATE_END DATE);

INSERT INTO XX_HOLIDAYS
VALUES ('PUBLIC HOLIDAYS',TO_DATE('14-11-2021','DD-MM-YYYY'),TO_DATE('16-11-2021','DD-MM-YYYY'));

COMMIT;

ATTENDANCE AVAILABE:
FOR personID 1 01-NOV-2021 TO 04-NOV-2021
for personID 2 01-NOV-2021 TO 03-NOV-2021
Leave Taken both : 07-NOV-2021 TO 09-NOV-2021
PUBLIC HOLIDAYS for all : 14-NOV-2021 TO 16-NOV-2021
Weekends : firday, saturday

Need to find employee didnt attend to office wihtout approval (ex: 10 nov 2021, 11 nov 2021 total 2 days he is absent)
1. excluding weekends friday, saturday,
2. excluding leaves(Absences)
3. excluding public holidays

Query output required as below:

select * from XX_ATTENDANCE_MAIN
where trans_date between TO_DATE('01-11-2021','DD-MM-YYYY') and TO_DATE('16-11-2021','DD-MM-YYYY');

PERSONID  TRANS_DATE IN_TIME OUT_TIME  ABSENT
1     11/1/2021 07:30   14:30
1     11/2/2021 07:30   14:30
1     11/3/2021 07:30   14:30
1     11/4/2021 07:30   14:30
1      11/10/2021  NULL     NULL   Y
1      11/11/2021  NULL     NULL   Y
2     11/1/2021 07:30   14:30
2     11/2/2021 07:30   14:30
2     11/3/2021 07:30   14:30
2     11/4/2021 NULL NULL Y
2      11/10/2021  NULL     NULL   Y
2     11/11/2021  NULL     NULL   Y

I tried using unionAll and minus dates, it becomes very long query .
Kindly suggest.

Thanks,
Afzal.

Update:
Our Database is 11g 11.2.0.4.0
Friday, Saturday are our weekends.
There is another main table of employees which holds all employee information ex: per_all_people_f.

This post has been answered by Afzal Baig on Nov 14 2021

Jump to Answer

Added on Nov 14 2021

#pl-sql, #plsql, #sql, #sqldeveloper, #sqlplus

8 comments

1,278 views

1 mention