jeudi 18 décembre 2014

How do I get the count for time periods?


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




Aucun commentaire:

Enregistrer un commentaire