Hi Team,
I am using oracle 12c Database.
i have one requirement to show the miss punch entries for employees wise or all employee between two dates.(one year/ One week or one days also user will run this report)
I using 4 tables like below Attendances, Calendar, Leave, Other visit. (One month sample script i added in the below)
Employee can register there leave (leave table) and other visit to client(othervisit table) and punch in details to the attendance machine and admin can configure the working days in the calendar table.
My requirement if attendance table and other three table don't get the employee log in details/Leave/Other visit details then for those missing days. i need to show like below.
employee id Missing Date Remarks
10001 24-nov-2022 MISS PUNCH IN
10001 25-nov-2022 MISS PUNCH IN
....
...
10001 30-nov-2022 MISS PUNCH IN
10002 07-nov-2022 MISS PUNCH IN
10002 16-nov-2022 MISS PUNCH IN
10003 01-nov-2022 MISS PUNCH IN
.....
10003 16-nov-2022 MISS PUNCH IN
10003 18-nov-2022 MISS PUNCH IN
.......
10003 30-nov-2022 MISS PUNCH IN
Employee - 1
Date : 7 to 16,18 to 23,24 to 30
7 TO 16 LEAVE
18 TO 23 OTHER VISIT
24 TO 30 MISS PUNCH IN
Employee - 2
Date : 6,7,8,16,18 to 30
6 - LEAVE
7- MISS PUNCH IN
8 TO 10 LEAVE
16 -MISS PUNCH IN
18 TO 30 OTHER VISIT
Employee - 3
1 TO 16 MISS PUNCH IN
18 TO 29 MISS PUNCH IN
--------------
--Script
Create table Attendances (att_id number,emp_id number,login_date date,
att_created_date date,created_by varchar2(100),att_type varchar2(100));
INSERT INTO Attendances VALUES(1,10001,TO_DATE('01-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(2,10001,TO_DATE('02-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(3,10001,TO_DATE('03-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(4,10001,TO_DATE('04-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(5,10001,TO_DATE('05-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(6,10001,TO_DATE('06-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(7,10001,TO_DATE('17-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(8,10002,TO_DATE('01-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(9,10002,TO_DATE('02-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(10,10002,TO_DATE('03-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(11,10002,TO_DATE('04-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(12,10002,TO_DATE('05-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(13,10002,TO_DATE('11-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(14,10002,TO_DATE('12-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(15,10002,TO_DATE('13-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(16,10002,TO_DATE('14-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(17,10002,TO_DATE('15-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(18,10002,TO_DATE('17-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(19,10003,TO_DATE('17-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
INSERT INTO Attendances VALUES(20,10003,TO_DATE('30-NOV-2022','DD-MM-RRRR'),SYSDATE,'ADMIN','IN');
Create table Calendar (ac_seq_no number,
ac_year number,
ac_date date,
ac_hol_flag varchar2(1) default 'N',
AC_CREATED_BY VARCHAR2(1000),
AC_CREATED_DATE DATE);
INSERT INTO Calendar VALUES (1,2022,TO_DATE('01-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (2,2022,TO_DATE('02-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (3,2022,TO_DATE('03-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (4,2022,TO_DATE('04-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (5,2022,TO_DATE('05-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (6,2022,TO_DATE('06-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (7,2022,TO_DATE('07-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (8,2022,TO_DATE('08-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (9,2022,TO_DATE('09-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (10,2022,TO_DATE('10-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (11,2022,TO_DATE('11-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (12,2022,TO_DATE('12-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (13,2022,TO_DATE('13-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (14,2022,TO_DATE('14-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (15,2022,TO_DATE('15-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (16,2022,TO_DATE('16-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (17,2022,TO_DATE('17-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (18,2022,TO_DATE('18-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (19,2022,TO_DATE('19-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (20,2022,TO_DATE('20-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (21,2022,TO_DATE('21-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (22,2022,TO_DATE('22-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (23,2022,TO_DATE('23-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (24,2022,TO_DATE('24-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (25,2022,TO_DATE('25-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (26,2022,TO_DATE('26-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (27,2022,TO_DATE('27-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (28,2022,TO_DATE('28-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (29,2022,TO_DATE('29-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
INSERT INTO Calendar VALUES (30,2022,TO_DATE('30-NOV-2022','DD-MM-RRRR'),'N','ADMIN',SYSDATE);
Create table lEAVES (LE_id number,LE_emp_id number,le_date_from date,le_date_TO date,
le_REJOIN_date date,
LE_created_date date,LE_created_by varchar2(100),lEAVE_type varchar2(100));
INSERT INTO lEAVES VALUES (1,10001,TO_DATE('07-NOV-2022','DD-MM-RRRR'),TO_DATE('16-NOV-2022','DD-MM-RRRR'),TO_DATE('17-NOV-2022','DD-MM-RRRR'),
SYSDATE,'ADMIN','V');
INSERT INTO lEAVES VALUES (2,10002,TO_DATE('06-NOV-2022','DD-MM-RRRR'),TO_DATE('06-NOV-2022','DD-MM-RRRR'),TO_DATE('07-NOV-2022','DD-MM-RRRR'),
SYSDATE,'ADMIN','S');
INSERT INTO lEAVES VALUES (3,10002,TO_DATE('08-NOV-2022','DD-MM-RRRR'),TO_DATE('10-NOV-2022','DD-MM-RRRR'),TO_DATE('11-NOV-2022','DD-MM-RRRR'),
SYSDATE,'ADMIN','S');
Create table othervisit (OV_id number,OV_emp_id number,OV_date_from date,OV_date_TO date,
OV_created_date date,OV_created_by varchar2(100));
INSERT INTO othervisit VALUES (1,10001,TO_DATE('18-NOV-2022','DD-MM-RRRR'),TO_DATE('23-NOV-2022','DD-MM-RRRR'),
SYSDATE,'ADMIN');
INSERT INTO othervisit VALUES (2,10002,TO_DATE('18-NOV-2022','DD-MM-RRRR'),TO_DATE('30-NOV-2022','DD-MM-RRRR'),
SYSDATE,'ADMIN');