• ColdCoffee

    SSC-Dedicated

    Points: 39971

    Another solution

    ; WITH CTE AS

    (

    SELECT EmployeeID

    ,EmployeeLoginDateTime = DATEADD(DD,DATEDIFF(DD,0,OutTab.EmployeeLoginDateTime),0)

    ,JobFunction = CASE WHEN JobFunction = 'Logout' THEN 1

    ELSE 0

    END

    FROM @TableA OutTab

    )

    SELECT EmployeeID

    , EmployeeLoginDateTime

    , 'Missed LogOut' Comment

    FROM CTE

    GROUP BY EmployeeID , EmployeeLoginDateTime

    HAVING SUM(JobFunction) = 0

    This solution is definitely faster than the previous one!

    Table '#32AB8735'. Scan count 2, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 86 ms.

    Table '#32AB8735'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 65 ms.