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

Asked to Learn

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

Karthick2003

Like this

SQL> select to_char(min(in_time), 'DD-MON-YYYY')attendance_date
  2       , to_char(max(in_time), 'hh:mi:ss AM') in_time
  3       , to_char(max(out_time), 'hh:mi:ss AM') out_time
  4    from (
  5            select ceil(row_number() over(order by md_time)/ 2) rno
  6                 , decode(md_machine_id, '01', md_time) in_time
  7                 , decode(md_machine_id, '02', md_time) out_time
  8              from machine_data
  9             where (md_time = trunc(md_time) + interval '17' hour and md_machine_id = '01')
 10                or (md_time = trunc(md_time) + interval '2'  hour and md_machine_id = '02')
 11         )
 12   group
 13      by rno;

ATTENDANCE_ IN_TIME     OUT_TIME
----------- ----------- -----------
01-JAN-2014 05:00:00 PM 02:00:00 AM
02-JAN-2014 05:00:00 PM 02:00:00 AM

SQL>

John Stegeman

You need to explain the logic for your results, because the output doesn't make sense to me.

Asked to Learn

Thanks John.

It's a good question and forget to mention it.

Fact is,

one or more employee has a shift from evening 5pm to night 2pm. And is this period he can go out and back. for example, he may enter office dated 01-jan-2014 at 4:45pm or 4:50pm or 5:00pm. and machine get the time with date 01-jan-2014.

But when when he left the office at 1:50am or 2.00am or 2:15am, machine get the time with date 02-jan-2014.

And when he/she enter office again at 02-jan-2014 same thing may happened.

he/she can go out and back in, in this office period and machine also stored the value.

One point is In and Out machine is different. For example 01 and 02.

What i need ?

I need his office in time and office out time.

Hope you get the logic.

Thanks again..

Ask2Learn.

Asked to Learn

Dear Karthick

Thanks for your reply.

According to my sample data your query is ok. but 5:00 PM and 2:00 AM is not fixed value. It can be different. For example output may be different for different days.

Example as

Date	In Time	Out Time
03-JAN-2014	05:10:00 PM	02:08:00 AM

/* in data */

INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
  VALUES (TO_DATE('03/01/2014 05:10:00 PM','DD/MM/RRRR HH:MI:SS AM'), '01' );

INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
  VALUES (TO_DATE('03/01/2014 11:20:00 PM','DD/MM/RRRR HH:MI:SS AM'), '01' );
/* out data */
INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
  VALUES (TO_DATE('03/01/2014 11:02:00 PM','DD/MM/RRRR HH:MI:SS AM'), '02' );

INSERT INTO MACHINE_DATA (MD_TIME, MD_MACHINE_ID )
  VALUES (TO_DATE('04/01/2014 02:08:00 AM','DD/MM/RRRR HH:MI:SS AM'), '02' );

Thanks for your time.

Message was edited by: Asked to Learn

Paula Scorchio

Suppose you saw this data

In   01/01/2014  17:00

Out 01/01/2014 23:50

In 02/01/2014 00:05

Out 02/01/2014  02:10

In 02/01/2014 17:00

Out 02/01/2014 21:00

As a human I would interpret this as

Turned up at 17:00

Left at 23:50 for a 15 minute break

Finished shift at 02:10

Turned up at 17:00

Finished shift at 21:00

Programmatically however, how does your code know that the 02:10 is the out time.  As a human I made an assumption that a gap of more than 3 hours represents an end of shift and that the next event should be the beginning of the next shift.

If the 3 hour ASSUMPTION is correct then to solve this problem, you effectively want to look for gaps between and Out and an In that exceed that timeframe, and use that gap as your end of shift marker.  You can then look for the events that define these gaps as the shift break.

HOWEVER,  What if the person pulls a double shift ?  Do you need to report that as one long shift or two discrete shifts.  If the latter how would you decide where the first shift ended and the second shift started ?

Once you have defined those rules, then you can come back and ask the proper SQL type question  (given a series of in and out DTTM values, how do I identify gaps of more than x hours between an Out and the next In. )

Asked to Learn

Dear  PaulaScorchio,

Thanks for your reply.

I also in doubt. I don't able to make any logic for double shift taking account the following shift.

Shift 1     08:00 AM          ~          05:00 PM

Shift 2     05:00 PM          ~          02:00 AM

Shift 3     10:00 PM          ~          08:00 AM

Does any body have any idea on this and can able to help ?

I will be very grateful

Ask2Learn

Frank Kulash

Hi,

It looks like the machine_data table records when work was actually done, but your business also has a concept of when work was scheduled to be done.  Scheduled work can't be derived from actual work; the two are completely independent.  You may have situations where an entire shift was skipped, that is, no actual work was done  during a scheduled shift.  In the other hand, you might have situations where work was done at unscheduled times.

Since a scheduled sift is independent of a actual work period, you probably want a separate table to record just the scheduled shifts.  Something like

CREATE TABLE  scheduled_work
(   machine_id  VARCHAR2 (20)
,   start_time  DATE
,   end_time    DATE
,   CONSTRAINT  sceduled_work_pk PRIMARY KEY (machine_id, start_time)
);

You can join this table to the machine_data table to answer questions like "How many hours of work were done per shift?" or "When did work on a shift actually begin?"

Partha Sarathy S

I am not clear about your logic. But this below query might help.

WITH T1 AS(
SELECT TO_DATE(ATTENDANCE_DATA,'DD-MON-YYYY') ATTENDANCE_DATA,
       ROW_NUMBER()OVER(PARTITION BY TO_CHAR(ATTENDANCE_DATA,'DD') ORDER BY 'A') RN_DATE,
       MD_TIME_IN,
       MD_TIME_OUT
FROM (
SELECT ATTENDANCE_DATA,
       MD_TIME_MOD MD_TIME_IN,
       LEAD(MD_TIME_MOD)OVER(ORDER BY MD_TIME_MOD) MD_TIME_OUT,
       MD_MACHINE_ID
FROM(
SELECT CASE WHEN (TO_CHAR(MD_TIME,'HH12 PM') < '02 PM') THEN TO_DATE(MD_TIME,'DD-MON-YYYY')-1
            ELSE MD_TIME END AS ATTENDANCE_DATA,
       TO_CHAR(MD_TIME,'DD-MON-YYYY HH12:MI:SS PM') MD_TIME_MOD,
        MD_MACHINE_ID
FROM MACHINE_DATA ORDER BY MD_TIME_MOD))
WHERE MD_MACHINE_ID=1)
SELECT TO_CHAR(ATTENDANCE_DATA,'DD-MON-YYYY') ATTENDANCE_DATA ,
       MIN(MD_TIME_IN) "In",
       MAX(MD_TIME_OUT) "Out"     
FROM T1 GROUP BY TO_CHAR(ATTENDANCE_DATA,'DD-MON-YYYY');

OUTPUT:

ATTENDANCE_DATA      In                               Out
-------------------- -------------------------------- -------------------------
01-JAN-2014          01-JAN-2014 05:00:00 PM          02-JAN-2014 02:00:00 AM
02-JAN-2014          02-JAN-2014 05:00:00 PM          03-JAN-2014 02:00:00 AM

WITH clause section gives the data part (obtained from input data) you have mentioned as "Consider the following data scenario".

user13179060

select t1.md_time,to_char(min(t2.md_time),'HH12:MI:SS AM') in_ti,

        to_char(max(t3.md_time),'HH12:MI:SS AM') out_ti

from (select distinct trunc(t1.md_time) md_time

from machine_data t1) t1,

      (select (t2.md_time) md_time,t2.md_machine_id from machine_data t2

       where t2.md_machine_id=01) t2,

       (select (t3.md_time) md_time,t3.md_machine_id from machine_data t3

       where t3.md_machine_id=02) t3

where (t2.md_time between t1.md_time+9/24 and t1.md_time+26/24)

and   (t3.md_time between t1.md_time+9/24  and t1.md_time+26/24)

group by t1.md_time

/