How to calculate the duration of an OverTime according to the InOutMode using sql ?

Question

1.00/5 (2 votes)

See more:

SQL-Server-2008R2

, +

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:

SQL

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 12-Jul-15 3:56am

Leila Toumi

Updated 1-Jun-20 2:44am

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2015-07-12T04:30:00

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

For such query something like:

SQL

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?

SQL

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