CODEWITHSUNDEEP
sqloracle-database
mdkamrul
mdkamrul

Reputation: 274

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

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

Upvotes: 0

Views: 974

Answers (2)

Popeye
Popeye

Reputation: 35920

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!!

Upvotes: 1

Erwin
Erwin

Reputation: 470

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

Upvotes: 0

Related Questions