Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

How can i achieve in time and out time from following data ?

Asked to LearnJan 3 2014 — edited Jan 6 2014

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 DataMachine-01 (In)Machine-02 (Out)
01-JAN-201401-JAN-2014 05:00:00 PM01-JAN-2014 07:00:00 PM
01-JAN-201401-JAN-2014 08:00:00 PM01-JAN-2014 10:00:00 PM
01-JAN-201401-JAN-2014 11:00:00 PM02-JAN-2014 01:00:00 AM
01-JAN-201402-JAN-2014 01:30:00 AM02-JAN-2014 02:00:00 AM
02-JAN-201402-JAN-2014 05:00:00 PM02-JAN-2014 10:00:00 PM
02-JAN-201402-JAN-2014 11:00:00 PM03-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 DataInOut
01-JAN-201405:00:00 PM02:00:00 AM
02-JAN-201405:00:00 PM05: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

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 3 2014
Added on Jan 3 2014
9 comments
372 views