Click here to Skip to main content
14,641,488 members

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

Leila Toumi asked:

Open original thread
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:

         DATEDIFF(Minute, TimesInOut, NextDate) as duration
FROM    (   SELECT  EnrollNumber, 
                    (   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 ?
Tags: SQL, SQL-Server (SQL-Server-2008R2, SQL-Server-2012)


When answering a question please:
  1. Read the question carefully.
  2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
  3. 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.
  4. 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.
Please note that all posts will be submitted under the The Code Project Open License (CPOL).

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