Count Data each shift schedule

Hi i’m trying to write query that will give me the count of duration that is greater than 10 each employee depending on his/her Shift schedule from tblshiftsched.

Shift A - 6AM - 2PM
Shift B - 2PM - 10PM
Shift C - 10PM - 6AM

tbleventlogs

EVENTID     USERID      DATE_TIME   READERID                 READERNAME LOGTYPE   RESULT DURATION

188110074       50078       2020-03-02 22:01:31 544381441       LOCKER      IN   00:12  12
188099752       50078       2020-03-02 21:48:52 544381436       LOCKER      OUT             
188098672       50078       2020-03-02 21:47:10 544381441       LOCKER      IN              
188098656       50078       2020-03-02 21:47:06 544381441       LOCKER      IN   15:45  945
187972279       50078       2020-03-02 06:01:54 544381436       LOCKER      OUT             
187949027       50078       2020-03-02 04:30:46 544381441       LOCKER      IN   00:16  16
187933475       50078       2020-03-01 22:44:47 544381431       LOCKER      IN   00:04  4
187933289       50078       2020-03-01 22:40:41 544381436       LOCKER      OUT             
187926256       50078       2020-03-01 22:01:31 544381431       LOCKER      IN   00:14  14
187914503       50078       2020-03-01 21:46:42 544381428       LOCKER      OUT  
180769437       1214201592  2020-01-20 08:13:06 544381436       LOCKER      OUT  
180771730       1214201592  2020-01-20 08:36:38 544381431       LOCKER      IN   00:23  23
180775099       1214201592  2020-01-20 09:07:04 544381436       LOCKER      OUT  
180777340       1214201592  2020-01-20 09:30:53 544381431       LOCKER      IN   00:23  23
180778948       1214201592  2020-01-20 09:48:58 544381436       LOCKER      OUT  
180781714       1214201592  2020-01-20 10:04:52 544381431       LOCKER      IN   00:15  15

tblemployeegroup

EMPLOYEEID  EMPLOYEENAME        GROUPCODE   

050078      ARCIAGA,        SC-G3-4G                    
1214201592  CALMA, VILMA LARIN  ASSR2*              
003502      ORRICA, MARY MELIN  ACBA1

tblshiftsched

SHIFTCODE   DATESHIFT       STARTSHIFT                      ENDSHIFT                                ISRESTDAY

SC-G3-4G           1/24/2020        1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM              0
SC-G3-4G        1/25/2020       1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM              0
SC-G3-4G        1/26/2020       1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM             0
SC-G3-4G        1/27/2020       1/1/1970 2:00:00.000000 PM  1/1/1970 10:00:00.000000 PM             0
SC-G3-4G        2/10/2020       1/1/1970 10:00:00.000000 PM 1/1/1970 6:00:00.000000 AM              0
SC-G3-4G        2/11/2020       1/1/1970 10:00:00.000000 PM 1/1/1970 6:00:00.000000 AM              0
SC-G3-4G        2/12/2020       1/1/1970 10:00:00.000000 PM 1/1/1970 6:00:00.000000 AM              0

i able to count data but its by each date and my problem is i only capture Shift A and Shift B because i get the data per day and Shift C takes 2 day

if the user is Shift C i on march 2 10 PM to March 3 6 AM it spans days

 SELECT   a.userid, a.username,
            TRUNC (TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS')
                  ) AS date_time,
            COUNT (a.DURATION) COUNT
       FROM tblaccesslogs a
      WHERE DURATION > 10
   GROUP BY a.username,
            a.userid,
            TRUNC (TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'));

Note this is related to my previous question my previous question

Hope you understand what i mean

UPDATE i have the query but i i can’t identify if the employee is early/late IN or early/late OUT. So if he is late out on shift A it count as shift B because he out on the time of Shift B.

  SELECT   a.userid, a.username,
            TRUNC(TO_DATE(A.DATE_TIME,'YYYY-MM-DD HH24:MI:SS')) AS DATE_TIME,
            case when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 6 and 13 then 'A'
                  when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 14 and 21 then 'B'
              else 'C' end as shift,
            COUNT (a.DURATION) COUNT
       FROM tblaccesslogs a
      WHERE DURATION > 10
   GROUP BY a.username,
            a.userid,
             TRUNC(TO_DATE(A.DATE_TIME,'YYYY-MM-DD HH24:MI:SS')),
            case when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 6 and 13 then 'A'
                  when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 14 and 21 then 'B'
              else 'C' end

Answer

Your query does not capture any shift, try something like this:

SELECT   a.userid, a.username,
            case when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 6 and 13 then 'A'
                  when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 14 and 21 then 'B'
              else 'C' end as shift,
            COUNT (a.DURATION) COUNT
       FROM tblaccesslogs a
      WHERE DURATION > 10
   GROUP BY a.username,
            a.userid,
            case when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 6 and 13 then 'A'
                  when to_char(TO_DATE (a.date_time, 'YYYY-MM-DD HH24:MI:SS'),'HH24') between 14 and 21 then 'B'
              else 'C' end

(I did not try the code, so may have left a ‘(‘ or something like that, I also left the to_date and did not extract the HH component just in case you have other formats than shown)

Advertisement

Answer