Time display query in sql

Question

I have following data

UserID  UserName  LogTime LogDate
1       S         9:00    21/5/2010
1       S         10:00   21/5/2010
1       S         11:00   21/5/2010
1       S         12:00   21/5/2010

Need Output as:-

1     s      9:00  10:00 21/5/2010
1     s      11:00 12:00 21/5/2010 

Answer 1

This will work on MS SQL (TSQL), with ROW_NUMBER() function:

SELECT l1.UserID, l1.UserName, l1.LogTime AS BeginTime, l2.LogTime AS EndTime,
  l1.LogDate, ROW_NUMBER() OVER (ORDER BY l1.LogDate, l1.LogTime) AS 'RowNumber'
FROM log l1
LEFT JOIN log l2
ON l2.UserID = l1.UserID
  AND l2.LogTime > l1.LogTime
  AND l2.LogDate = l1.LogDate
LEFT JOIN log l3
ON l3.UserID = l1.UserID
  AND l3.LogTime < l2.LogTime AND l3.LogTime > l1.LogTime
  AND l3.LogDate = l1.LogDate
WHERE l3.UserID IS NULL AND RowNumber % 2 = 1 AND l1.UserID = 1
ORDER BY l1.LogDate, l1.LogTime

The first join, joins the first time with all next times for the same user and date. The second join allows us to exclude all times on the same date and user that have a time between them, thus giving us sequential times. The ROW_NUMBER function allows us to only grab every other row so we get 9:00 - 10:00, but not 10:00 - 11:00.

If there is no end time, you will get NULL for EndTime.

Note that for periods that stretch from one day to the next, you will need to adjust the query to combine the time and date and compare them together.

You should not try to return results for more than one user, otherwise, you will lose data due to the mod function.