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