Skip to Main Content
Forums

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!

Attendance and Leave table Join

Syed KhawarAug 6 2012 — edited Aug 7 2012
Hi,
I want to join both of the following tables "Attendance" and "Leave", common key is EMP# = EMPLOYEE_NUMBER but the problem is that the Leave table stores range(start and end date) of leaves not daily record in this case how can I generate each day record from Leave table(where leaves defined as Start and End date) to join with Attendance table or any other solution anyone suggest.

Thanks & Regards,
Syed Khawar


Attendance Table 
--CREATE ATTENDANCE TABLE
CREATE TABLE ATTENDANCE
(EMP_ID VARCHAR2(20), ATT_DATE DATE, 
 TIMEIN DATE, TIMEOUT DATE, LATE_IN VARCHAR2(10), EARLY_OUT VARCHAR2(10), REASON VARCHAR2(10));
 
--INSERT INTO ATTENDANCE
 Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/01/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:31:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:02:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/02/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:31:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:05:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/03/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:18:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:18:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '07:42');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/07/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:47:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:06:00', 'MM/DD/YYYY HH24:MI:SS'), '00:17', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/08/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:44:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:14:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/09/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:20:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:08:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/10/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:28:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:02:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:26:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 14:59:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/14/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:25:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:10:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:42:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:07:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/16/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:20:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:08:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/17/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:38:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:06:00', 'MM/DD/YYYY HH24:MI:SS'), '00:00', 
    '00:00');
Insert into ATTENDANCE
   (EMP_ID, ATT_DATE, TIMEIN, TIMEOUT, LATE_IN, EARLY_OUT)
 Values
   ('0265', TO_DATE('07/18/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 07:54:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2012 15:02:00', 'MM/DD/YYYY HH24:MI:SS'), '00:24', 
    '00:00');
COMMIT;

--SAMPLE DATA
EMP_ID	ATT_DATE	TIMEIN	TIMEOUT	LATE_IN	EARLY_OUT	REASON
265	01/07/2012	01/08/2012 7:31	01/08/2012 15:02	0:00	0:00	
265	02/07/2012	01/08/2012 7:31	01/08/2012 15:05	0:00	0:00	
265	03/07/2012	01/08/2012 7:18	01/08/2012 7:18	0:00	7:42	
265	07/07/2012	01/08/2012 7:47	01/08/2012 15:06	0:17	0:00	
265	08/07/2012	01/08/2012 7:44	01/08/2012 15:14	0:00	0:00	
265	09/07/2012	01/08/2012 7:20	01/08/2012 15:08	0:00	0:00	
265	10/07/2012	01/08/2012 7:28	01/08/2012 15:02	0:00	0:00	
265	11/07/2012	01/08/2012 7:26	01/08/2012 14:59	0:00	0:00	
265	14/07/2012	01/08/2012 7:25	01/08/2012 15:10	0:00	0:00	
265	15/07/2012	01/08/2012 7:42	01/08/2012 15:07	0:00	0:00	
265	16/07/2012	01/08/2012 7:20	01/08/2012 15:08	0:00	0:00	
265	17/07/2012	01/08/2012 7:38	01/08/2012 15:06	0:00	0:00	
265	18/07/2012	01/08/2012 7:54	01/08/2012 15:02	0:24	0:00
Leave Table 
--CREATE LEAVE TABLE
CREATE TABLE LEAVE 
(
  EMPLOYEE_NUMBER  VARCHAR2(30 BYTE),
  LEAVE_TYPE       VARCHAR2(30 BYTE)            NOT NULL,
  LEAVE_REASON     VARCHAR2(240 BYTE),
  DATE_START       DATE,
  DATE_END         DATE
);

--INSERT LEAVE TABLE
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Emergency leave', 'Emergency', TO_DATE('07/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('07/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Annual Leave', 'Vacation', TO_DATE('06/23/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/25/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Emergency leave', 'Emergency', TO_DATE('06/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/04/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Annual Leave', 'Vacation', TO_DATE('02/12/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/14/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Annual Leave', 'Vacation', TO_DATE('03/08/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/08/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Annual Leave', 'Vacation', TO_DATE('06/18/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/19/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Annual Leave', 'Vacation', TO_DATE('07/03/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('07/06/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Annual Leave', 'Vacation', TO_DATE('08/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into LEAVE
   (EMPLOYEE_NUMBER, LEAVE_TYPE, LEAVE_REASON, DATE_START, DATE_END)
 Values
   ('0265', 'Annual Leave', 'Vacation', TO_DATE('08/20/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/20/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

--SAMPLE DATA
EMPLOYEE_NUMBER	LEAVE_TYPE	LEAVE_REASON	DATE_START	DATE_END
0265	Emergency leave	Emergency	04/07/2012	04/07/2012
0265	Annual Leave	Vacation	23/06/2012	25/06/2012
0265	Emergency leave	Emergency	04/06/2012	04/06/2012
0265	Annual Leave	Vacation	12/02/2011	14/02/2011
0265	Annual Leave	Vacation	08/03/2011	08/03/2011
0265	Annual Leave	Vacation	18/06/2011	19/06/2011
0265	Annual Leave	Vacation	03/07/2011	06/07/2011
0265	Annual Leave	Vacation	01/08/2011	01/08/2011
0265	Annual Leave	Vacation	20/08/2011	20/08/2011

--Expected Output

EMP_ID	ATT_DAY	ATT_DATE	TIMEIN	TIMEOUT	LATE_IN	EARLY_OUT	REASON
0265	Sunday   	01/07/2012	7:31	15:02	0:00	0:00	
0265	Monday   	02/07/2012	7:31	15:05	0:00	0:00	
0265	Tuesday  	03/07/2012	7:18	7:18	0:00	7:42	
0265	Wednesday	04/07/2012					Emergency
0265	Saturday 	07/07/2012	7:47	15:06	0:17	0:00	
0265	Sunday   	08/07/2012	7:44	15:14	0:00	0:00	
0265	Monday   	09/07/2012	7:20	15:08	0:00	0:00	
0265	Tuesday  	10/07/2012	7:28	15:02	0:00	0:00	
0265	Wednesday	11/07/2012	7:26	14:59	0:00	0:00	
0265	Saturday 	14/07/2012	7:25	15:10	0:00	0:00	
0265	Sunday   	15/07/2012	7:42	15:07	0:00	0:00	
0265	Monday   	16/07/2012	7:20	15:08	0:00	0:00	
0265	Tuesday  	17/07/2012	7:38	15:06	0:00	0:00	
0265	Wednesday	18/07/2012	7:54	15:02	0:24	0:00
Fixed Attendance table creation & Insert statements

Edited by: Syed Khawar on Aug 6, 2012 10:09 PM
This post has been answered by Frank Kulash on Aug 6 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 4 2012
Added on Aug 6 2012
6 comments
1,214 views