Home » SQL & PL/SQL » SQL & PL/SQL » how to get employee in and out time for generated query based on first in and last out (Oracle 11g)
how to get employee in and out time for generated query based on first in and last out [message #689081] Mon, 18 September 2023 01:29 Go to next message
murad060
Messages: 3
Registered: September 2023
Location: Bangladesh
Junior Member

Urgent help from experts, I have an ‚Äćattendance table, where data is stored from an attendance device, how can I separate an employer in punch time and out punch time from this table?

My Table Data Is
/forum/fa/14743/0/

My requirement output is Like

Date                 EMP_ID           In_Time      Out_Time
04/Jul/22           201913836        3:42:55 PM    3:43:03 PM
Re: how to get employee in and out time for generated query based on first in and last out [message #689082 is a reply to message #689081] Mon, 18 September 2023 02:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68500
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


A simple MIN and MAX grouping by TRUNC(DATETIME) and EMP_ID would do the work.

Re: how to get employee in and out time for generated query based on first in and last out [message #689083 is a reply to message #689082] Mon, 18 September 2023 02:22 Go to previous messageGo to next message
murad060
Messages: 3
Registered: September 2023
Location: Bangladesh
Junior Member

Thanks For the Reply, Can you give me a code sample please I am a new developer in the Oracle platform.
Re: how to get employee in and out time for generated query based on first in and last out [message #689084 is a reply to message #689083] Mon, 18 September 2023 04:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68500
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you provide a test case so I can show you.

Correction in my first post: "A simple MIN and MAX grouping by PUNCH_DATE and EMP_ID would do the work."

[Updated on: Mon, 18 September 2023 04:11]

Report message to a moderator

Re: how to get employee in and out time for generated query based on first in and last out [message #689085 is a reply to message #689084] Mon, 18 September 2023 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68500
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Using the standard EMP table:
SQL> select deptno, hiredate, to_number(to_char(trunc(hiredate,'YEAR'),'YYYY')) "YEAR" from emp;
    DEPTNO HIREDATE          YEAR
---------- ----------- ----------
        20 17-DEC-1980       1980
        30 20-FEB-1981       1981
        30 22-FEB-1981       1981
        20 02-APR-1981       1981
        30 28-SEP-1981       1981
        30 01-MAY-1981       1981
        10 09-JUN-1981       1981
        20 19-APR-1987       1987
        10 17-NOV-1981       1981
        30 08-SEP-1981       1981
        20 23-MAY-1987       1987
        30 03-DEC-1981       1981
        20 03-DEC-1981       1981
        10 23-JAN-1982       1982

14 rows selected.

SQL> select to_number(to_char(trunc(hiredate,'YEAR'),'YYYY'))"YEAR", deptno,
  2          to_char(min(hiredate),'Mon-DD') "First hired",
  3          to_char(max(hiredate),'Mon-DD') "Last hired"
  4  from emp
  5  group by trunc(hiredate,'YEAR'),  deptno
  6  order by trunc(hiredate,'YEAR'),  deptno
  7  /
      YEAR     DEPTNO First  Last h
---------- ---------- ------ ------
      1980         20 Dec-17 Dec-17
      1981         10 Jun-09 Nov-17
      1981         20 Apr-02 Dec-03
      1981         30 Feb-20 Dec-03
      1982         10 Jan-23 Jan-23
      1987         20 Apr-19 May-23
Re: how to get employee in and out time for generated query based on first in and last out [message #689091 is a reply to message #689081] Mon, 18 September 2023 11:27 Go to previous messageGo to next message
mathguy
Messages: 77
Registered: January 2023
Member
Quote:
AAttachment: Machine_Data.png
This is a no-no. Any IT employee who opens binary attachments on a public website from an unknown poster should be fired immediately. In today's world, security should be taken seriously.

Besides, a png file (an image) is useless for our purposes. You need to post data in a format that can be copied and pasted into a client software like SQL Developer. CREATE TABLE and INSERT statements. (If you use SQL Developer yourself, or other similar software, even SQL*Plus, you would only need to use a few commands to generate such CREATE TABLE and INSERT statements automatically.)

Also, always include your Oracle database version (SELECT * FROM V$VERSION); different SQL tools exist in different versions.

Now back to your question.

You only show one row in the output. Is that really what you need? What happens to the rest of your data?

In your sample inputs, there are two timestamps for 04-JUL. But the next three timestamps are on different dates, days apart: on 07-JUL, 14-JUL and 18-JUL. So what happened there - and how can we help you sort this out? Does an employee punch in on July 7 and works for seven days without interruption, then they punch out on July 14? Or does this mean that the clock device is broken and doesn't register all events? Or does the employee forget to punch in or out?

In any of these scenarios, there is nothing you can do in the database (or with a computer); what do you expect here?

Or is your real-life data different, it doesn't have such huge gaps between events? If so, then your sample is bad; it has to be a simple illustration of the problem, but it shouldn't be misleading.

Are there exactly two events per employee per day? Then what Michel has shown makes sense. In real life, though, an employee may be in and out more than once per day (for example if they take a lunch break, or they have an approved doctor appointment in the middle of the day, etc.) An employee may also work at night - punch in at 22:00 and out at 06:30 the next morning. In those cases Michel's solution won't help. But we can't help you beyond this point, without more clarity from you.
Re: how to get employee in and out time for generated query based on first in and last out [message #689092 is a reply to message #689085] Mon, 18 September 2023 11:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9058
Registered: November 2002
Location: California, USA
Senior Member
In the following minimal example, dates with only one punch time will have the same date for in and out times.  Also, whatever your first column is "M." (machine?) is ignored.  It is ordered by the punch_date, which is really just a truncation of the datetime, then emp_id.  You could reverse the order of those, depending on how you want it displayed.  It would have been better if you had included create table and insert statements for sample data that included multiple "M." and emp_id values and the results that you want based on those values, instead of data for one employee and only results for one row.


-- test table and minimal relevant data:
SCOTT@orcl_12.1.0.2.0> SELECT emp_id,
  2  	    TO_CHAR (datetime, 'DD-MON-YYYY HH:MI:SS PM') AS punch
  3  FROM   machine_data
  4  ORDER  BY emp_id, datetime
  5  /

    EMP_ID  PUNCH
----------  --------------------------------
 201913836  04-JUL-2022 03:42:55 PM
 201913836  04-JUL-2022 03:43:03 PM
 201913836  18-JUL-2022 09:39:58 PM
 201913836  18-JUL-2022 09:40:04 PM

4 rows selected.

-- optional settings:
SCOTT@orcl_12.1.0.2.0> SET    COLSEP	 '  '
SCOTT@orcl_12.1.0.2.0> COLUMN "Date"	 FORMAT A11
SCOTT@orcl_12.1.0.2.0> COLUMN "In_Time"  FORMAT A11
SCOTT@orcl_12.1.0.2.0> COLUMN "Out_Time" FORMAT A11

-- query:
SCOTT@orcl_12.1.0.2.0> SELECT TO_CHAR (TRUNC (datetime), 'DD/Mon/YYYY') AS "Date",
  2  	    emp_id,
  3  	    TO_CHAR (MIN (datetime), 'HH:MI:SS PM') AS "In_Time",
  4  	    TO_CHAR (MAX (datetime), 'HH:MI:SS PM') AS "Out_Time"
  5  FROM   machine_data
  6  GROUP  BY TRUNC (datetime), emp_id
  7  ORDER  BY TRUNC (datetime), emp_id
  8  /

Date             EMP_ID  In_Time      Out_Time
-----------  ----------  -----------  -----------
04/Jul/2022   201913836  03:42:55 PM  03:43:03 PM
18/Jul/2022   201913836  09:39:58 PM  09:40:04 PM

2 rows selected.





Re: how to get employee in and out time for generated query based on first in and last out [message #689094 is a reply to message #689092] Mon, 18 September 2023 13:49 Go to previous messageGo to next message
murad060
Messages: 3
Registered: September 2023
Location: Bangladesh
Junior Member

Many many thanks for your help, I really appreciate your help.
Re: how to get employee in and out time for generated query based on first in and last out [message #689096 is a reply to message #689094] Tue, 19 September 2023 08:30 Go to previous message
Solomon Yakobson
Messages: 3244
Registered: January 2010
Location: Connecticut, USA
Senior Member
This doesn't sound like a real case. There is no way to get correct results without knowing if date is punch in or punch out date. We ccan only assume first date is punch in date. And using group by trunc(datetime) isn't right either. What if employee worked night shift or had to stay late and punched out after midnight. Assuming employee first datetime is punch in:

with t as (
           select  emp_id,
                   datetime datetime_in,
                   lead(datetime) over(partition by emp_id order by datetime) datetime_out,
                   row_number() over(partition by emp_id order by datetime) rn
             from  machine_data
          )
select  emp_id,
        datetime_in,
        datetime_out
  from  t
  where mod(rn,2) = 1
  order by emp_id,
           rn
/
SY.
Previous Topic: Update statement with INNER JOIN
Next Topic: Jpeg Image thumbnail,height,width properties need in Oracle 19c
Goto Forum:
  


Current Time: Fri Sep 22 16:03:10 CDT 2023