Hello Experts,
Good Day.
I'm using
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
"CORE 10.2.0.3.0 Production"
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Consider the following data scenario.
Here His/Her Shift is 5:00:00 PM to 2:00:00 AM, Total 9 Hours
| Attendance Data | Machine-01 (In) | Machine-02 (Out) |
|---|
| 01-JAN-2014 | 01-JAN-2014 05:00:00 PM | 01-JAN-2014 07:00:00 PM |
| 01-JAN-2014 | 01-JAN-2014 08:00:00 PM | 01-JAN-2014 10:00:00 PM |
| 01-JAN-2014 | 01-JAN-2014 11:00:00 PM | 02-JAN-2014 01:00:00 AM |
| 01-JAN-2014 | 02-JAN-2014 01:30:00 AM | 02-JAN-2014 02:00:00 AM |
| | |
| 02-JAN-2014 | 02-JAN-2014 05:00:00 PM | 02-JAN-2014 10:00:00 PM |
| 02-JAN-2014 | 02-JAN-2014 11:00:00 PM | 03-JAN-2014 02:00:00 AM |
I need a SQL what shows only his/her IN Time (entered in office) and OUT Time (left the office).
Like
| Attendance Data | In | Out |
|---|
| 01-JAN-2014 | 05:00:00 PM | 02:00:00 AM |
| 02-JAN-2014 | 05:00:00 PM | 05:00:00 PM |
Here is the script
CREATE TABLE MACHINE_DATA
(
MD_TIME DATE
, MD_MACHINE_ID VARCHAR2(20)
);
/* IN DATA INSERT */
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('01/01/2014 05:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '01' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('01/01/2014 08:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '01' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('01/01/2014 11:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '01' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('02/01/2014 01:30:00 AM','DD/MM/RRRR HH:MI:SS AM'), '01' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('02/01/2014 05:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '01' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('02/01/2014 11:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '01' );
/* OUT DATA INSERT */
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('01/01/2014 07:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '02' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('01/01/2014 10:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '02' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('02/01/2014 01:00:00 AM','DD/MM/RRRR HH:MI:SS AM'), '02' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('02/01/2014 02:00:00 AM','DD/MM/RRRR HH:MI:SS AM'), '02' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('02/01/2014 10:00:00 PM','DD/MM/RRRR HH:MI:SS AM'), '02' );
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
VALUES (TO_DATE('03/01/2014 02:00:00 AM','DD/MM/RRRR HH:MI:SS AM'), '02' );
01 for in and 02 for out
Advance thanks for your reply.
Asked2Learn
Message was edited by: Asked to Learn