Click here to Skip to main content
14,603,943 members
Rate this:
Please Sign up or sign in to vote.
I'm trying to calculate the duration of the overtime for each employee.
I'm using the Log Table: Log_Attendance:

EnrollNumber    DateAttendance     TimesInOut                        ModeEvent   
1               12-07-2015          12-07-2015 14:00:00                 4
1               12-07-2015          12-07-2015 15:00:00                 5
1               12-07-2015          12-07-2015 17:00:00                 4
1               12-07-2015          12-07-2015 18:00:00                 4
1               12-07-2015          12-07-2015 19:00:00                 5


ModeEvent= 4 --> OverTimeOut
ModeEvent= 5 --> OverTimeIn

I need that the system give NULL when the employee has consecutive rows with the same ModeEvent (Example row 3: the employee has an overtimeIN but he hasn't marked his OvertimeOut)

I tried this query but it gives me a wrong values:

SELECT  
        EnrollNumber,
        TimesInOut,
        NextDate,
        Mode,
      
    
         DATEDIFF(Minute, TimesInOut, NextDate) as duration
      
        
FROM    (   SELECT  EnrollNumber, 
                    ID,
                    TimesInOut,
                    Mode,
                    DateAttendance,
                    (   SELECT  MIN(TimesInOut) 
                        FROM    Log_Attendance T2
                        WHERE   T2.EnrollNumber = T1.EnrollNumber
                        AND     T2.TimesInOut > T1.TimesInOut
                         and T2.Mode <> T1.Mode
                         
                    ) AS NextDate
            FROM    Log_Attendance T1
           where mode in (4)
        ) AS T
        
 where mode in (4,5) and EnrollNumber=1




Any Solution please ?
Posted
Updated 1-Jun-20 2:44am
v2
Comments
Michael_Davies 12-Jul-15 10:16am
   
Looking at your example presume 4=In, not Out and 5=Out not in...

Why not consider holding in and out in two separate fields in the table, it would make it a lot easier to perform calculations and to determine missing sign outs. Default the out time to be midnight on an early date, like 01/01/1980 to signify that it has not been signed out. Then all you need is a WHERE out<>01/01/1980 to catch all the signed out overtime and change it to = to catch the unsigned-out...
Leila Toumi 12-Jul-15 10:59am
   
I can't because I have another EventMode like CheckIn, CheckOut, BreakIn and breakOut
Leila Toumi 12-Jul-15 10:19am
   
thanks for your reply but can you give me an example please.
Michael_Davies 12-Jul-15 10:31am
   
I updated my comment.

Have two columns in your table, one for time in and one for time out, set the default to the time out to be a known early date as I suggest.

You can then SELECT using DATEDIFF on the two fields without any complex sub-selects.

This also helps when verifying a sign out, for instance I assume someone ought not to be able to sign out the next day an overtime sign in from a previous day or whatever rule you want to apply regarding overtime hours. If you do it as you have it then all your UPDATEs and SELECTs are going to be complex.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Did I understand correctly that you want NULL in case there is an IN mark but not OUT.

For such query something like:
SELECT l1.*, NULL as overtime
FROM Log_Attendance l1
WHERE l1.ModeEvent = 4  -- select IN events
AND   5 <> (SELECT ModeEvent -- search for the corresponding out event
            FROM Log_Attendance l2
            WHERE l2.EnrollNumber = l1.EnrollNumber 
            AND   l2.TImesInOut = (SELECT MIN(L3.TImesInOut) -- based on the next event in time
                                   FROM Log_Attendance l3
                                   WHERE l3.EnrollNumber = l1.EnrollNumber
                                   AND L3.TImesInOut > l1.TimesInOut))

You can switch the condition vice versa if you want to have the rows with out mark but no corresponding in mark and use UNION to combine the result sets.

ADDITION
Something like this?
SELECT EnrollNumber,
       '' AS desription,
       DATEDIFF(hour, l2.TimesInOout, l1.TimesInOut) as time
FROM Log_Attendance l1,
     Log_Attendance l2
WHERE l1.ModeEvent = 4
AND   l2.ModeEvent = 5
AND   l1.EnrollNumber = l2.EnrollNumber
AND   l2.TImesInOut = (SELECT MIN(L3.TImesInOut) -- based on the next event in time
                       FROM Log_Attendance l3
                       WHERE l3.EnrollNumber = l1.EnrollNumber
                       AND L3.TImesInOut > l1.TimesInOut))
UNION ALL
SELECT EnrollNumber,
       'End time missing' AS desription,
       NULL as time
FROM Log_Attendance l1
WHERE l1.ModeEvent = 4  -- select IN events
AND   5 <> (SELECT ModeEvent -- search for the corresponding out event
            FROM Log_Attendance l2
            WHERE l2.EnrollNumber = l1.EnrollNumber 
            AND   l2.TImesInOut = (SELECT MIN(L3.TImesInOut) -- based on the next event in time
                                   FROM Log_Attendance l3
                                   WHERE l3.EnrollNumber = l1.EnrollNumber
                                   AND L3.TImesInOut > l1.TimesInOut))
   
v4
Comments
Leila Toumi 12-Jul-15 10:53am
   
I want that it calculate the duration between 2 overtimein and overtimeout ana if the employee has an in without out , it displays null in the column of the overtimeduration
Wendelius 12-Jul-15 11:55am
   
I modified the query to include records having in and out marks. However I'm not sure about the conditions since you're the only one who knows the logic for the data. This means that you may have to add additional conditions or change the ones I wrote.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100