T-SQL query - GROUP BY issue

Question

I have a table in SQL Server consisting of two columns that track how many users are logged into a system at a particular interval. The first column is Time15, which is the time rounded to the nearest 15 minute interval (datetime). The second column is UserCount, which is the number of users logged into the system during that 15 minute interval.

I need a query that will show me the peak number of users logged in for each day. So if at 1 PM on 1/1/09 the system reached a peak of 100 users, I need output that looks like:

1/1/09 - 100

1/2/09 - 94

1/3/09 - 98

etc ...

Any idea what T-SQL is going to accomplish this? Thanks!

Answer 1

Try this:

SELECT YEAR(Time15), MONTH(Time15), DAY(Time15), MAX(UserCount)
FROM Table
GROUP BY YEAR(Time15), MONTH(Time15), DAY(Time15)

I like Russ's approach for getting rid of the time portion - if you have SQL Server 2005+ you can use this approach to preserve the time of day the peak happened:

WITH cte AS 
(
SELECT Time15, UserCount, ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(d, 0, Time15), 0) ORDER BY UserCount DESC) AS RowNumber
FROM <Table>
)
SELECT Time15, UserCount
FROM cte
WHERE RowNumber = 1

Answer 2

You basically need to drop the time part from the Time15 field in order to get the Date. Then it's just a simple case of getting the Maximum UserCount when you group by the Date.

SELECT
    DATEADD(dd, DATEDIFF(d, 0, Time15), 0) As [Date],
    MAX(UserCount) As [Maximum Users]
FROM
    Table
GROUP BY
     DATEADD(dd, DATEDIFF(d, 0, Time15), 0)

Answer 3

I'd just convert it this way...seems to be the simplest..

SELECT convert(varchar,Time15,101) as da, max(UserCount)FROM TABLE GROUP BY convert(varchar,Time15,101)