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.
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