CODEWITHSUNDEEP
sqloracle-databaseoracle11ggreatest-n-per-group
Kiffer Van
Kiffer Van

Reputation: 242

How to get first and last data on each day per employee

Hi i'm trying to write a query that will give me output of

users first IN and last out each day where reader name is not Locker and identify whether he is late IN or early OUT

sample data:

EVENTID USERID  DATE_TIME   READERID    READERNAME  SNAME   LOGTYPE USERNAME    RESULT  REMARKS DURATION

177792329   50078   2020-01-02 05:45:34 544381431   LOCKER  RF-T-33 [IN]    IN   SUBIA, MAY             
177792497   50078   2020-01-02 05:45:50 544343461   SENSORS RF-T-19 [IN]    IN   SUBIA, MAY             
177813432   50078   2020-01-02 06:35:51 544352385   SENSORS RF-T-20 [OUT]   OUT  SUBIA, MAY             
177813495   50078   2020-01-02 06:36:13 544381436   LOCKER  RF-T-36 [OUT]   OUT  SUBIA, MAY             
177950118   50078   2020-01-03 05:50:48 544352385   SENSORS RF-T-20 [OUT]   OUT  SUBIA, MAY             
177954602   50078   2020-01-03 05:54:11 544381436   LOCKER  RF-T-36 [OUT]   OUT  SUBIA, MAY             
177967366   50078   2020-01-03 06:07:25 544381431   LOCKER  RF-T-33 [IN]    IN   SUBIA, MAY     00:13       13
177967448   50078   2020-01-03 06:07:38 544343461   SENSORS RF-T-19 [IN]    IN   SUBIA, MAY             
177977184   50078   2020-01-03 08:02:59 544352385   SENSORS RF-T-20 [OUT]   OUT  SUBIA, MAY             
177977214   50078   2020-01-03 08:03:21 544381436   LOCKER  RF-T-36 [OUT]   OUT  SUBIA, MAY             
177979653   50078   2020-01-03 08:23:54 544381431   LOCKER  RF-T-33 [IN]    IN   SUBIA, MAY     00:20       20

expected output

first IN

USERNAME    DATE_TIME   First_IN_Time
 SUBIA, MAY     2020-01-02 05:45:50   05:45:34 
SUBIA, MAY      2020-01-03 05:45:50   06:07:25

last out

USERNAME    DATE_TIME   last_out_Time
 SUBIA, MAY     2020-01-02 06:35:51  06:35:51
SUBIA, MAY      2020-01-03 05:45:50   08:02:59

tblshift

  SHIFTCODE   DATESHIFT       STARTSHIFT                      ENDSHIFT                                ISRESTDAY

    50078           1/24/2020        1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM              0
    50078        1/25/2020       1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM              0
    50078        1/26/2020       1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM             0
    50078        1/27/2020       1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM             0
    50078        2/10/2020       1/1/1970 10:00:00.000000 PM 1/1/1970 6:00:00.000000 AM              0
    50078        2/11/2020       1/1/1970 10:00:00.000000 PM 1/1/1970 6:00:00.000000 AM              0
    50078        2/12/2020       1/1/1970 10:00:00.000000 PM 1/1/1970 6:00:00.000000 AM

what i have try

Select
  max(A.DATE_TIME) keep (dense_rank first order by A.DATE_TIME) first_IN,
 --Max(A.DATE_TIME)  first_IN,
 A.USERNAME 
--min (A.DATE_TIME ) first_IN, max (A.DATE_TIME ) last_out,A.USERNAME 
--case
--end
from tblaccesslogs a 

where A.LOGTYPE = 'IN' and A.READERNAME <> 'LOCKER'
 group by  A.USERNAME

Update i need to identify whether employee is late in our early out base on the table tblshift

Hope some one help me out with this.

Upvotes: 0

Views: 76

Answers (2)

MT0
MT0

Reputation: 167981

Use the ROW_NUMBER analytic function:

create table tblaccesslogs (
  EVENTID,
  USERID,
  DATE_TIME,
  READERID,
  READERNAME,
  SNAME,
  LOGTYPE,
  USERNAME
) AS
SELECT 177792329,   50078,  DATE '2020-01-02' + INTERVAL '05:45:34' HOUR TO SECOND, 544381431,   'LOCKER',  'RF-T-33', 'IN',   'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177792497,   50078,  DATE '2020-01-02' + INTERVAL '05:45:50' HOUR TO SECOND, 544343461,   'SENSORS', 'RF-T-19', 'IN',   'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177813432,   50078,  DATE '2020-01-02' + INTERVAL '06:35:51' HOUR TO SECOND, 544352385,   'SENSORS', 'RF-T-20', 'OUT',  'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177813495,   50078,  DATE '2020-01-02' + INTERVAL '06:36:13' HOUR TO SECOND, 544381436,   'LOCKER',  'RF-T-36', 'OUT',  'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177950118,   50078,  DATE '2020-01-03' + INTERVAL '05:50:48' HOUR TO SECOND, 544352385,   'SENSORS', 'RF-T-20', 'OUT',  'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177954602,   50078,  DATE '2020-01-03' + INTERVAL '05:54:11' HOUR TO SECOND, 544381436,   'LOCKER',  'RF-T-36', 'OUT',  'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177967366,   50078,  DATE '2020-01-03' + INTERVAL '06:07:25' HOUR TO SECOND, 544381431,   'LOCKER',  'RF-T-33', 'IN',   'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177967448,   50078,  DATE '2020-01-03' + INTERVAL '06:07:38' HOUR TO SECOND, 544343461,   'SENSORS', 'RF-T-19', 'IN',   'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177977184,   50078,  DATE '2020-01-03' + INTERVAL '08:02:59' HOUR TO SECOND, 544352385,   'SENSORS', 'RF-T-20', 'OUT',  'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177977214,   50078,  DATE '2020-01-03' + INTERVAL '08:03:21' HOUR TO SECOND, 544381436,   'LOCKER',  'RF-T-36', 'OUT',  'SUBIA, MAY' FROM DUAL UNION ALL
SELECT 177979653,   50078,  DATE '2020-01-03' + INTERVAL '08:23:54' HOUR TO SECOND, 544381431,   'LOCKER',  'RF-T-33', 'IN',   'SUBIA, MAY' FROM DUAL;

Then:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY USERNAME, LOGTYPE, TRUNC( DATE_TIME )
           ORDER BY DATE_TIME ASC
         ) AS first_date_time_rn
  FROM   tblaccesslogs t
  WHERE  READERNAME <> 'LOCKER'
)
WHERE  logtype = 'IN'
AND    first_date_time_rn = 1

outputs:

  EVENTID | USERID | DATE_TIME           |  READERID | READERNAME | SNAME   | LOGTYPE | USERNAME   | FIRST_DATE_TIME_RN
--------: | -----: | :------------------ | --------: | :--------- | :------ | :------ | :--------- | -----------------:
177792497 |  50078 | 2020-01-02 05:45:50 | 544343461 | SENSORS    | RF-T-19 | IN      | SUBIA, MAY |                  1
177967448 |  50078 | 2020-01-03 06:07:38 | 544343461 | SENSORS    | RF-T-19 | IN      | SUBIA, MAY |                  1

and:

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY USERNAME, LOGTYPE, TRUNC( DATE_TIME )
           ORDER BY DATE_TIME DESC
         ) AS last_date_time_rn
  FROM   tblaccesslogs t
  WHERE  READERNAME <> 'LOCKER'
)
WHERE  logtype = 'OUT'
AND    last_date_time_rn = 1

outputs:

  EVENTID | USERID | DATE_TIME           |  READERID | READERNAME | SNAME   | LOGTYPE | USERNAME   | LAST_DATE_TIME_RN
--------: | -----: | :------------------ | --------: | :--------- | :------ | :------ | :--------- | ----------------:
177813432 |  50078 | 2020-01-02 06:35:51 | 544352385 | SENSORS    | RF-T-20 | OUT     | SUBIA, MAY |                 1
177977184 |  50078 | 2020-01-03 08:02:59 | 544352385 | SENSORS    | RF-T-20 | OUT     | SUBIA, MAY |                 1

db<>fiddle here

Upvotes: 0

Popeye
Popeye

Reputation: 35910

You can use an aggregate function to fetch the in and out time in a single query as following and compare it with ideal shift start time as following:

SELECT
    T.USERNAME,
    CASE
        WHEN FIRST_IN_TIME > 
            TRUNC(FIRST_IN_TIME) + ( STARTSHIFT - DATE '1970-01-01' ) THEN 'LATE'
        ELSE 'ONTIME'
    END AS IN_STATUS
FROM
    (
        SELECT
            A.USERNAME,
            A.USERID,
            TRUNC(A.DATE_TIME) AS DATE_TIME,
            MIN(CASE WHEN A.LOGTYPE = 'IN' THEN A.DATE_TIME END) AS FIRST_IN_TIME,
            MAX(CASE WHEN A.LOGTYPE = 'OUT' THEN A.DATE_TIME END) AS LAST_OUT_TIME
        FROM TBLACCESSLOGS A
        WHERE A.READERNAME <> 'LOCKER'
        GROUP BY A.USERNAME, A.USERID, TRUNC(A.DATE_TIME)
    ) T
    JOIN TBLSHIFTS S ON T.USERID = S.SHIFTCODE AND TRUNC(T.DATE_TIME) = TRUNC(S.DATESHIFT);

Cheers!!

Upvotes: 1

Related Questions