This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,926 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

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

Asked to Learn
Asked to Learn Member Posts: 173 Blue Ribbon
edited Jan 6, 2014 12:43PM in SQL & PL/SQL

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

Tagged:

Answers

  • Karthick2003
    Karthick2003 IndiaMember Posts: 13,711 Bronze Badge

    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>
    Karthick2003
  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond

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

    John Stegeman
  • Asked to Learn
    Asked to Learn Member Posts: 173 Blue Ribbon

    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
    Asked to Learn Member Posts: 173 Blue Ribbon
    edited Jan 3, 2014 1:17PM

    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

    DateIn TimeOut Time
    03-JAN-201405:10:00 PM02: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

  • 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
    Asked to Learn Member Posts: 173 Blue Ribbon

    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
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond

    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
    Partha Sarathy S Member Posts: 1,200 Silver Trophy

    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
    user13179060 Senior Software Engineer ChennaiMember Posts: 45

    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

    /

This discussion has been closed.