Oracle query for get employee in and out time for generated query based on first in and lastout

Question

I wanted to get employee attendance based on in/our at my oracle database table column. The column name is Direction which have in/out value and time have at another column. my problem is that I am getting in and out time more times for a employee sine they are are do in and out more than one times with their card pance system. I wanted to get only first in time for each employee and last out time for each employee. Here is my query for this

select
fullname,direction,to_char(((TO_DATE('19700101','yyyymmdd') + (tstamp/24/60/60) + 6/24)), 'dd-mm-yyyy  hh12:mi:ss PM') as Time,
gate,lane,employment.employeeid,NATIONALID,departmentname,designation.designationname
from eofficeuat.entrylog_cpa
join eofficeuat.employee on entrylog_cpa.hrrecordid=employee.hrrecordid
join eofficeuat.employment on employee.hrrecordid=employment.hrrecordid
join eofficeuat.designation on employment.designationid=designation.designationid
join eofficeuat.department on employment.departmentid=department.departmentid
where department.departmentname = 'SECURITY'
and tstamp >= 1568763700 and tstamp < (select (sysdate - date '1970-01-01') * 86400000 from dual)
order by fullname desc;

here is data for the query

fullname        Direction   Time
--------        ---------   ----------

Utpal Dhar      In          18-09-2019  08:02:00 AM
Utpal Dhar      In          18-09-2019  08:04:31 AM
Utpal Dhar      In          18-09-2019  08:04:35 AM
TOPAZZAL HOSEN  In          19-09-2019  07:57:51 AM
TOPAZZAL HOSEN  In          18-09-2019  07:56:39 AM
TOPAZZAL HOSEN  out         18-09-2019  04:13:18 PM
TOPAZZAL HOSEN  out         18-09-2019  12:36:23 PM

this query giving all in and out time for each employee, but I need only row which will first in time with in direction and last out time with last direction.

Here is rows as I am expecting

fullname        Direction   Time
--------        ---------   ----------

Utpal Dhar      In          18-09-2019  08:02:00 AM
TOPAZZAL HOSEN  In          18-09-2019  07:56:39 AM
TOPAZZAL HOSEN  out         18-09-2019  04:13:18 PM

Please help me with this

Popeye · Accepted Answer · 2019-10-01T06:35:52.470

1

You can use the analytical function -- ROW_NUMBER() in such a situation:

SELECT
    FULLNAME,
    DIRECTION,
    TIME,
    ROUND((MAX_TIME - MIN_TIME)*24, 2) AS DURATION
FROM
    (
        SELECT
            T.*,
            MIN(CASE
                WHEN T.DIRECTION = 'In' THEN TO_DATE(TIME, 'dd-mm-yyyy hh12:mi:ss PM')
            END) OVER(
                PARTITION BY FULLNAME, TRUNC(TO_DATE(TIME, 'dd-mm-yyyy hh12:mi:ss PM'))
            ) AS MIN_TIME,
            MAX(CASE
                WHEN T.DIRECTION = 'Out' THEN TO_DATE(TIME, 'dd-mm-yyyy hh12:mi:ss PM')
            END) OVER(
                PARTITION BY FULLNAME, TRUNC(TO_DATE(TIME, 'dd-mm-yyyy hh12:mi:ss PM'))
            ) AS MAX_TIME
        FROM
            ( YOUR_QUERY ) T
    )
WHERE
    TO_DATE(TIME, 'dd-mm-yyyy hh12:mi:ss PM') IN (
        MIN_TIME,
        MAX_TIME
    );

Cheers!!

edited Oct 01 '19 at 06:35

answered Sep 19 '19 at 04:45

Popeye

35,427
4
10
31

this showing me this error "ORA-01722: invalid number 01722. 00000 - "invalid number" *Cause: The specified number was invalid. *Action: Specify a valid number." – mdkamrul Sep 19 '19 at 04:52
Because the time column in your query is not a date, you convert it to date, before using it in outer query. Also, This query will give you day-wise IN and OUT for each employee – Popeye Sep 19 '19 at 05:12
time is stored in my table as unix timestamp. this have in eofficeuat.entrylog_cpa table and I have converted it to to_date at main query – mdkamrul Sep 19 '19 at 05:16
you are my hero this working but still have some bug like I am getting a employee who have in time but getting out time. Can you please check it again? – mdkamrul Sep 19 '19 at 05:58
I think same employee name not coming twice, but I need same employee name should be twice with in and out time if have – mdkamrul Sep 19 '19 at 06:13
You are correct, I have updated the query. Please check now!! – Popeye Sep 19 '19 at 06:43
this returning zero row – mdkamrul Sep 19 '19 at 06:59
Check the updated answer now!! Case when is added into the WHERE clause – Popeye Sep 19 '19 at 08:06
showing this error "ORA-00904: "T"."DIRECTION": invalid identifier 00904. 00000 - "%s: invalid identifier"" – mdkamrul Sep 19 '19 at 08:10
That is in WHERE clause, You can remove that (t.) Anyway, Updated the answer – Popeye Sep 19 '19 at 08:12
ok, but I am still getting in time row for a employee not out time – mdkamrul Sep 19 '19 at 08:16
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/199676/discussion-between-tejash-and-mdkamrul). – Popeye Sep 19 '19 at 08:18
one more help, can i find hours difference between in and out time? – mdkamrul Oct 01 '19 at 04:47
(date1 - date2) * 24 – Popeye Oct 01 '19 at 05:20
Updated the answer – Popeye Oct 01 '19 at 05:59
I am getting the duration like this https://gyazo.com/2b206a76ba267e0b761bc2c6dc91f562 . please check – mdkamrul Oct 01 '19 at 06:27
I am not getting this as hours https://gyazo.com/54e23efbc506caa339d67aed116d5fc4 – mdkamrul Oct 01 '19 at 06:49
What is your dates for which 0.61 is showing as an hour? – Popeye Oct 01 '19 at 07:00
https://gyazo.com/58f747cb0b3db06cb42cd627e65c34af here is complete result, the time columns are dates – mdkamrul Oct 01 '19 at 07:05
I think it is giving correct output. It is difference in hours between out and in time. What is your expectation apart from 0.61? – Popeye Oct 01 '19 at 07:07
my expectation is diffrence between in and out time as hours. here is the two time in = 30-09-2019 01:19:18 PM and out = 30-09-2019 01:55:55 PM diffrence showing 0.61 . is 0.61 is correct hours? – mdkamrul Oct 01 '19 at 07:14
Yes,0.61 is correct hours. You can count the minutes using (0.61*60) = 36.6 minutes – Popeye Oct 01 '19 at 07:50
yes but i not getting it correct for all agent like https://gyazo.com/d4452850cc63894f8171a57272870695 . if you see the first two row in = 29-09-2019 12:26:51 PM and out = 29-09-2019 04:15:28 PM duration is 228.62 as hours this not making sense by the way I have updated the query at answer – mdkamrul Oct 01 '19 at 08:01
can you also come on chat please? – mdkamrul Oct 01 '19 at 08:07
can you let me know please how can calculate total rows returned from this query? – mdkamrul Oct 06 '19 at 05:13
Use `count(1) over()` after calculation of maxtime and add it in select clause to see total rows which will display total rows count - same number in each row – Popeye Oct 06 '19 at 05:45
can you please tell me how can i merge in and out rows in one row with time so Fullname not will show twice with 2 rows? so should be in one row with in and out time like the image https://gyazo.com/50280e3fdf0fe5d3c6ea535f92608469 – mdkamrul Oct 16 '19 at 09:38
can you please make this solution for me? I also have open a new question here https://stackoverflow.com/questions/58411584/oracle-query-for-combine-two-rows-data-into-two-columns-data – mdkamrul Oct 17 '19 at 03:55

Erwin · Answer 2 · 2019-09-19T05:21:47.193

0

Order it by time then select the top row use rownum. Here is the way you can use.

SELECT A.FULLNAME, B.DIRECTION, B.TIME 
FROM eofficeuat.employee A
JOIN (
//YOUR QUERY GOES HERE
select
fullname,direction,to_char(((TO_DATE('19700101','yyyymmdd') + (tstamp/24/60/60) + 6/24)), 'dd-mm-yyyy  hh12:mi:ss PM') as Time,
gate,lane,employment.employeeid,NATIONALID,departmentname,designation.designationname
from eofficeuat.entrylog_cpa
join eofficeuat.employee on entrylog_cpa.hrrecordid=employee.hrrecordid
join eofficeuat.employment on employee.hrrecordid=employment.hrrecordid
join eofficeuat.designation on employment.designationid=designation.designationid
join eofficeuat.department on employment.departmentid=department.departmentid
where department.departmentname = 'SECURITY'
and tstamp >= 1568763700 and tstamp < (select (sysdate - date '1970-01-01') * 86400000 from dual)
WHERE ROWNUM = 1 // the rownum should be here
order by fullname desc;
//YOUR QUERY GOES HERE
) B
ON A.FULLNAME = B.FULLNAME
GROUP BY A.FULLNAME, B.DIRECTION, B.TIME

This should work

edited Sep 19 '19 at 05:21

answered Sep 19 '19 at 04:05

Erwin

460
2
6

if i use rownum = 1 then I am getting only one row. – mdkamrul Sep 19 '19 at 04:12
I have edited my answer, please check if the query should work – Erwin Sep 19 '19 at 04:18
the fullname have in eofficeuat.employee table this query showing me this error " ORA-00904: "A"."FULLNAME": invalid identifier 00904. 00000 - "%s: invalid identifier" – mdkamrul Sep 19 '19 at 04:25
I have edit the query in my answer, please check again – Erwin Sep 19 '19 at 04:30
I am still getting one row and just full name column. also can you say what should be at " //your query goes here " comment? – mdkamrul Sep 19 '19 at 04:32
in the block "//your query goes here" I just copy and paste your query that you put in the question, so basically I just make your query into subquery to join from a table that contain fullname, then filter it with each fullname which contain rownum=1 (only the 1st row for each fullname). For the column you should add another column you need in the main query. – Erwin Sep 19 '19 at 04:37
ok but my query which have in block does not working I meant I am not getting columns and rows as I the query have in block. I am getting only one row and one column which is full name – mdkamrul Sep 19 '19 at 04:41
I have edit it again, I have mistakenly put the rownum filter. Please check again – Erwin Sep 19 '19 at 04:51
sorry this showing this error "ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" – mdkamrul Sep 19 '19 at 05:01
Forgot to add all column described in group by expression. I have add all column now. Should be working now. – Erwin Sep 19 '19 at 05:23
still i am getting only one row – mdkamrul Sep 19 '19 at 05:58

Oracle query for get employee in and out time for generated query based on first in and lastout

2 Answers2