Fix cross applying rows when records are duplicated

Question

I have an EVENT table which contains enter and exit events. I calculate work time with this procedure:

SET @worktime = (SELECT SUM(mins)
                 FROM
                     (SELECT
                          entry.EmployeeId, entry.DateTime AS EntryDateTime, 
                          [exit].DateTime AS ExitDateTime, DATEDIFF(MINUTE, entry.DateTime, [exit].DateTime) AS mins
                      FROM
                          Events entry
                      CROSS APPLY 
                          (SELECT TOP 1 e.DateTime
                           FROM Events e
                           WHERE e.EmployeeId = entry.EmployeeId
                             AND e.DateTime > entry.DateTime
                             AND e.EventTypeID = 2
                             AND CAST(e.DateTime AS DATE) = CAST(@data AS DATE)
                             AND e.ControlPointID IN (SELECT ControlPointID 
                                                      FROM ControlPoints 
                                                      INNER JOIN dbo.Split(RIGHT(@rcp, LEN(@rcp) - 2), ';') AS split ON ControlPoints.NAme = split.Data + ' EXIT')
                           ORDER BY e.DateTime ASC) AS [exit]
                      WHERE 
                          entry.EventTypeId = 1 
                          AND EmployeeId = @code 
                          AND CAST(entry.DateTime AS DATE) = CAST(@data AS DATE) 
                          AND ControlPointID IN (SELECT ControlPointID 
                                                 FROM ControlPoints 
                                                 INNER JOIN dbo.Split(RIGHT(@rcp, LEN(@rcp) - 2), ';') AS split ON ControlPoints.NAme = split.Data + ' ENTRY') 
                                                                                                                AND CAST(entry.[DateTime] AS DATE) = CAST(@data AS DATE)) AS input
                 GROUP BY EmployeeId)

For example, where there are two enter and two exit events for some Employee, it works perfect. But, if there is one exit and two enter events, it does not work like I would like to work.

Example: Employee XXX comes to work at 05:44, then he exits at 06:28. Later he comes back at 06:50 and exits again at 12:33. This procedure should return @worktime equal to 387 minutes.

Next day he makes a mistake, he comes to work at 06:00, but in the EVENTS table, there are two identical records. He exits at 14:00 (just 1 exit record in EVENT table). Procedure returns 960 minutes. I would like to if it returns 480.

How can I fix cross applying when the number of enter events count don't equal the number of exit events (like in example: 2 enters and 1 exit)?

Example with Images:

Below example works perfect.

Selected Rows (1,4,5,8) are passed to procedure. ControlPointId = 6 is Entry Point, ControlPointId = 3 is Exit Point. EventTypeId = 1 is the enter event and EventTypeId = 2 is the exit.

But this does not work:

In this case, ControlPointId = 64 is the entry point and ControlPointId = 56 is the exit point. As we can see, this Employee made mistake and ControlPoint read his card twice, so there are 2 entry events and 1 exit event.

How should I change the procedure? It should take only first entry event when there are mistakes like this one (or in opposite situation: 1 entry and 2 exit events).

Answer 1

You can try this. If different entry events have same exit event, I keep first entry by using ROW_NUMBER

SET @worktime = (SELECT SUM(mins)
                 FROM
                     (SELECT
                          entry.EmployeeId, entry.DateTime AS EntryDateTime, 
                          [exit].DateTime AS ExitDateTime, 
                          DATEDIFF(MINUTE, entry.DateTime, [exit].DateTime) AS mins,
                          RN = ROW_NUMBER () OVER (PARTITION BY [exit].EventID ORDER  BY entry.DateTime)
                      FROM
                          Events entry
                      CROSS APPLY 
                          (SELECT TOP 1 e.DateTime, e.EventID
                           FROM Events e
                           WHERE e.EmployeeId = entry.EmployeeId
                             AND e.DateTime > entry.DateTime
                             AND e.EventTypeID = 2
                             AND CAST(e.DateTime AS DATE) = CAST(@data AS DATE)
                             AND e.ControlPointID IN (SELECT ControlPointID 
                                                      FROM ControlPoints 
                                                      INNER JOIN dbo.Split(RIGHT(@rcp, LEN(@rcp) - 2), ';') AS split ON ControlPoints.NAme = split.Data + ' EXIT')
                           ORDER BY e.DateTime ASC) AS [exit]
                      WHERE 
                          entry.EventTypeId = 1 
                          AND EmployeeId = @code 
                          AND CAST(entry.DateTime AS DATE) = CAST(@data AS DATE) 
                          AND ControlPointID IN (SELECT ControlPointID 
                                                 FROM ControlPoints 
                                                 INNER JOIN dbo.Split(RIGHT(@rcp, LEN(@rcp) - 2), ';') AS split ON ControlPoints.NAme = split.Data + ' ENTRY') 
                                                                                                                AND CAST(entry.[DateTime] AS DATE) = CAST(@data AS DATE)) AS input
                 WHERE RN = 1
                 GROUP BY EmployeeId)