SQL group or where by?

I have a table where we log users who have logged on to our system. It logs when they log on and when they log off. I need to produce some SQL which gives us a list of hours of the day and the number of people logged on in that time. I can do this for one time, but I do not know how to extend this to produce each hour of the day. I don't really want to have to do 开发者_如何学编程24 SQL statements if possible!?

SELECT     COUNT(userID) AS "count of users"
FROM         LogonTimes
WHERE     (LoginTime > '2011-09-12 09:00:00') AND (LogoffTime < '2011-09-12 10:00:00 ')

The above would produce say "3". This shows 3 people were logged on between 9 and 10am.

Any ideas?!

[edit]It's SQL Server as people have guessed below - sorry for not specifying! I'll try out the suggestions and post back shortly! thanks :) [/edit]

For SQL Server you could do...

declare @DayToCheck datetime 
set @DayToCheck = '2011-09-12'

;with C as
(
  select @DayToCheck as H
  union all
  select dateadd(hour, 1, H)
  from C
  where dateadd(hour, 1, H) < dateadd(day, 1, @DayToCheck)
)
select C.H as [hour],
       count(L.userID) as [count of users]
from C
  left outer join LogonTimes as L
    on L.LogoffTime > C.H and 
       L.LoginTime < dateadd(hour, 1, C.H)
group by C.H       

Try here: https://data.stackexchange.com/stackoverflow/q/112462/

A version for SQL Server 2000 that uses a number table instead if a CTE. Here I use master..spt_values.

declare @DayToCheck datetime
set @DayToCheck = '2011-09-12'

select dateadd(hour, N.number, @DayToCheck) as [hour],
       count(L.userID) as [count of users]
from master..spt_values as N
  left outer join LogonTimes as L
    on L.LogoffTime > dateadd(hour, N.number, @DayToCheck) and
       L.LoginTime < dateadd(hour, N.number + 1, @DayToCheck)
where N.Type = 'P' and
      N.Number between 0 and 23       
group by dateadd(hour, N.number, @DayToCheck)       

Round the LoginTime column to the nearest hour then group by that.

To Martin's point, if your desire is to have a total count users logged in during the timeframe, you will need to also add some other conditions to the where clause, i.e.

logged in before the current hour and logged out during the current hour

logged in during the current hour and logged off after the current hour

logged in before the current hour and logged off after the current hour

You might even have to account for unrecorded logoff datetimes, depending on how your logoff system works.

It is unclear from the question what environment you are working in (SQL SERVER vs. Oracle vs Other). The following would be specific to Oracle:

SELECT    
    HOUR_OF_DAY,
    COUNT(userID) AS "count of users"
FROM         LogonTimes,
(
    select TRUNC(SYSDATE)+COUNTER/24  AS HOUR_OF_DAY from 
    (
        select 
            level-1 as COUNTER 
        from dual  
        connect by level <=24
    ) a
    where COUNTER >= 0 
)
WHERE     (LoginTime >= HOUR_OF_DAY) AND (LogoffTime < HOUR_OF_DAY+1/24)

Note: this, like your original, counts the people who log in during the hour, not necessarily everybody logged in (i.e. they were already logged in, and stayed logged in for several hours).

EDIT: If you want to catch everybody logged in at any point during the hour, the WHERE clause should be:

WHERE     (LoginTime < HOUR_OF_DAY+1/24) -- logged in before end of the hour
 AND (LogoffTime > HOUR_OF_DAY) --logged off after start of the hour

As you've not specified an SQL language, I'm going to use SQL Server. I'm making use of table-value functions for my hours, but you could use temporary tables to do the same thing. The function needed is:

CREATE FUNCTION [dbo].[NumbersBetween] (
    @start int,
    @end int
) RETURNS @ret TABLE (Num int) AS BEGIN

    DECLARE @x int

    SET @x = @start
    WHILE (@x <= @end) BEGIN
        INSERT INTO @ret VALUES(@x)
        SET @x = @x + 1
    END

    RETURN
END
GO

The table for testing this is:

CREATE TABLE LogonTimes(
    SessionId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    LoginTime datetime NOT NULL,
    LogOffTime datetime NULL
)
INSERT INTO LogonTimes(LoginTime, LogOffTime) VALUES('2011-09-12 09:10', '2011-09-12 10:10')

Finally, the function to get the information you want (assuming you want this per day, not just for all days) is

DECLARE @queryDate datetime
SET @queryDate = '2011-09-12'

SELECT DATEADD(hour, hours.Num, @queryDate) AS HourOfDay, COUNT(LogonTimes.SessionId) AS SessionCount
FROM dbo.NumbersBetween(0, 23) hours 
  LEFT JOIN LogonTimes 
    ON LogonTimes.LoginTime < DATEADD(hour, hours.Num + 1, @queryDate) 
      AND ISNULL(LogonTimes.LogOffTime, GETDATE()) > DATEADD(hour, hours.Num, @queryDate)
GROUP BY hours.Num
ORDER BY HourOfDay

This gives the following result (truncated to remove the blanks at the start and end)

HourOfDay               SessionCount
----------------------- ------------
....
2011-09-12 07:00:00.000 0
2011-09-12 08:00:00.000 0
2011-09-12 09:00:00.000 1
2011-09-12 10:00:00.000 1
2011-09-12 11:00:00.000 0
....

This solution accounts for users who are still logged on, users who logged on and off within the same hour, and users who stayed on past midnight.

--

Updated Solution to account for only querying a single day, removing the SQL2008 dependencies.