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)
|
|
|
|
|
|
|
|
| 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   |
 |
Michel Cadot
Messages: 68508
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 |
 |
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 |
 |
Barbara Boehmer
Messages: 9059
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 #689096 is a reply to message #689094] |
Tue, 19 September 2023 08:30 |
Solomon Yakobson
Messages: 3246
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
/
|
|
|
|
Goto Forum:
Current Time: Thu Sep 28 02:15:34 CDT 2023
|
] 
Blog
Search
Help
Members
Register
Login
Home
