SQL Server Performance

Sum the Lock Time from two tables

Discussion in 'SQL Server 2005 General Developer Questions' started by baburk, Sep 10, 2010.

  1. baburk New Member

    Hi All,
    DECLARE
    @Lock TABLE(LogInID INT, UserName VARCHAR(50), IpAddress VARCHAR(50), LogInDate DATETIME)INSERT
    INTO @LockSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 1:05AM' UNION ALLSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:00AM' UNION ALLSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:14AM' UNION ALLSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 3:02AM' UNION ALLSELECT 11, 'rkbabu', '111.111.111.111', 'Aug 24 2010 11:38PM' DECLARE
    @UnLock TABLE(LogInID INT, UserName VARCHAR(50), IpAddress VARCHAR(50), LogInDate DATETIME)INSERT
    INTO @UnLockSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 1:12AM' UNION ALLSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:03AM' UNION ALLSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 2:43AM' UNION ALLSELECT
    10, 'rkbabu', '111.111.111.111', 'Aug 24 2010 3:02AM' UNION ALLSELECT
    11, 'rkbabu', '111.111.111.111', 'Aug 24 2010 11:50PM'SELECT
    * FROM @LockSELECT * FROM @UnLock
    I want to get the result as difference between 'Aug 24 2010 1:05AM' in @Lock and 'Aug 24 2010 1:12AM' in @UnLock
    'Aug 24 2010 2:00AM' in @Lock and 'Aug 24 2010 2:03AM' in @UnLock and so on.
    We should use group by LogInDate, UserName, IpAddress
    Finnally the difference of time should be sumed for LogInDate, UserName, IpAddress
    Thanks,
    Babu Kumarasamy.
  2. FrankKalis Moderator

    Sounds like a homework assignment, but nontheless here it goes:
    SELECT
    L.LogInID, MIN(L.UserName) AS UserName, L.IpAddress, DATEDIFF(MINUTE, L.LogInDate, MIN(UL.LogInDate)) AS LockPeriod
    FROM
    @Lock L
    LEFT JOIN
    @UnLock UL ON L.LogInID = UL.LogInID AND L.LogInDate <= UL.LogInDate
    GROUP BY
    L.LogInID, L.LogInDate, L.IpAddress

Share This Page