Sql-server – How to get the count for time periods

sql server

I have a table with the following columns.

[ComputerGroupid], [Computerid], [Userid], [loginTime], [logoffTime]

And my requirement is to get the table with the below information

[ComputerGroupid], [StartTime], [endTime], [numberOfcomputerBeingUsed]

But I am not sure how to merge the date ranges when they overlap and also when the number of computers being used are equal?

edit:
Some more details.
What I am looking for is to get a table of computer usage for a group of computers. so I am expecting to create a table where when ordered by time the [numberOfcomputerBeingUsed] column goes up as people log into computers and the [StartTime] and [endTime] columns will match up.

eg: if 4 people come into a room and login at about the same time, for an hour, and one person comes in and logs on for 5 mins and then leaves. I would expect:

 [ComputerGroupid] | [Computerid] | [Userid] | [loginTime] | [logoffTime]
 A                 | 1            | 1        | 09:00       | 10:00
 A                 | 2            | 2        | 09:01       | 10:01
 A                 | 3            | 3        | 09:02       | 10:04
 A                 | 4            | 4        | 09:03       | 10:03
 A                 | 5            | 5        | 09:10       | 09:15 

  [StartTime]  |  [endTime]  |  [numberOfcomputerBeingUsed]
  09:00        |  09:01      |  1
  09:01        |  09:02      |  2
  09:02        |  09:03      |  3
  09:03        |  09:04      |  4
  09:10        |  09:15      |  5
  09:15        |  10:00      |  4
  10:00        |  10:01      |  3
  10:01        |  10:02      |  2
  10:02        |  10:03      |  1
  10:03        |  10:04      |  0

Best Answer

This is what worked for me in the end.

SELECT 
     [StartTime].[PossibleTime] AS [StartTime]
    ,[EndTime].[PossibleTime] AS [EndTime]
    ,(SELECT COUNT(1) 
       FROM [Log] AS [L] 
       WHERE [StartTime].[PossibleTime] >= [L].[loginTime]
       AND [EndTime].[PossibleTime] <= [L].[logoutTime]
     ) AS [UserCount]
SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM]
FROM (
    SELECT [loginTime] AS [PossibleTime]
    FROM [Log]
    UNION ALL
    SELECT [logoutTime] AS [PossibleTime]
    FROM [Log]
   ) AS [EndTime]
INNER JOIN
SELECT ROW_NUMBER() OVER (ORDER BY [PossibleTime]) AS [ROWNUM]
FROM (
    SELECT [loginTime] AS [PossibleTime]
    FROM [Log]
    UNION ALL
    SELECT [logoutTime] AS [PossibleTime]
    FROM [Log]
) AS [StartTime]
ON [StartTime].[ROWNUM] = [EndTime].[ROWNUM]
ORDER BY [StartTime].[PossibleTime], [EndTime].[PossibleTime]
Related Question