Home Forums SQL Server 2008 T-SQL (SS2K8) Help Required to handle multiple employees with overlapping datetimes (concurrent activities) RE: Help Required to handle multiple employees with overlapping datetimes (concurrent activities)

  • I must admit to only having had a quick glance at this, but couldn't you do something like this instead?

    SELECT EmployeeId, MIN(LoginTime) AS LoginTime, MAX(LogOutTime) AS LogOutTime

    FROM (SELECT DENSE_RANK() OVER(PARTITION BY base.EmployeeId ORDER BY base.LoginTime),

    base.EmployeeId, base.LoginTime, overLap.LogOutTime

    FROM Overlapping base

    INNER JOIN Overlapping overLap ON base.EmployeeId = overLap.EmployeeId

    WHERE (overLap.LoginTime > Base.LoginTime AND overLap.LoginTime < base.LogOutTime)

    OR (overLap.LoginTime = base.LoginTime AND overLap.LogOutTime > base.LogOutTime)

    AND base.DKey <> overLap.DKey

    )a(pos,EmployeeId,LoginTime,LogOutTime)

    GROUP BY pos, EmployeeId;

    Don't know whether or not it'd be any faster. . . could probably do with a performance check but I haven't really got the time to knock up a million row sample test to check.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/