How to get record count that overlaps for a give period of time in SQL?

I have lot of users (hundre开发者_运维知识库d of thousands), and I would like to calculate peak usage of the servers to which they are connected to and interact with.

I maintain a table a very simple table with the UserID, BeginDT, the DateTime when session started and EndDT also a DateTime when it ended.

I would like to know for each hour of the day, how much users were connected. I may want it for the minute of day too.

I can't add a new field in that table to count the number of users connected at a given time because I don't control the application that fills the table.

I need to generate a dataset with the amount of users connected at a given time of the day for the selected period to plot a graph.

---

I'm not entirely sure what you're looking for. The below will give you the breakdown of count BY HOUR this does not separate out different days. This uses a tally table, please see the link if you need information on this. Right now if the user logs in at 10:59, they will still be counted in the 10:00 hour.

CREATE TABLE #LogTimes
(
    userId int,
    starttime DATETIME,
    endtime DATETIME
)

 SELECT TOP 24
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1

INSERT INTO #LogTimes
SELECT 1, GETDATE(), DATEADD(HH, 3, GETDATE()) UNION ALL
SELECT 2, DATEADD(DD, 1, GETDATE()), GETDATE() UNION ALL
SELECT 3, GETDATE(), DATEADD(HH, 5, GETDATE()) UNION ALL
SELECT 4, GETDATE(), DATEADD(HH, 7, GETDATE()) UNION ALL
SELECT 5, GETDATE(), DATEADD(HH, 9, GETDATE())

SELECT N, COUNT(*) FROM #LogTimes LT
    JOIN Tally T ON DATEPART(Hour, startTime) <= T.N AND DATEPART(hour, endtime) >= T.N
    GROUP BY N
    ORDER BY N ASC  

Results:

10  5
11  4
12  4
13  4
14  3
15  3
16  2
17  2
18  1
19  1

Also, this will not work correctly if the user crosses days. This logic will need to be added.

---

well you query the db and select the count UserID's that have BeginDT>=your start time and EndDT<=your start time + 1 (hour/min/sec/day), best is to use timestamps it is way more faster.

Something like

SELECT count(UserID) FROM users Where BeginDT>="starttime" AND EndDT<="startime+timeincrement"

Note: This is 1 query for only specific hour(day, etc.) If you want to get a period with the peaks, it varies very much what you exactly would like to receive, probably you would need to run multiple selects.

---

If you have LINQ available, this will do it:

var start = new DateTime(2008, 1, 1);
var end = new DateTime(2008, 1, 2);
var q1 = TblSessions.Where (s => s.BeginDT >= start && s.EndDT <= end).ToList();
var q2 = from r in Enumerable.Range(0, (int)(end - start).TotalHours).Select (e => start.AddHours(e))           
         select new 
         {
            Time = r,
            Count = q1.Count (x => r >= x.BeginDT && r <= x.EndDT)
         };

(You can fill and query a dataset if you don't have Linq2Sql with the same query)