May 14, 2008 at 6:50 am
Hi there,
I have a table (Sessions) which consists of 3 columns:
Date (DD:MM:YY hrs:min:sec), Concurrentusers (int), Application (nvchar(256))
What I need to be able to do is to report the maximum number of concurrent users that occurred for each application on each day (and the time that happened).
Using the SQL:
Select Date, Max(concurrentusers), Application
from sessions
Group by Date, Application
Returns me all of the Date entries. But I only want to see the Date (and time!) when the maximum number of concurrent users per application was reached.
Any help very gratefully received....
JJ
May 14, 2008 at 7:03 am
You need to do a group by convert(varchar(10),Date,120),Application
May 14, 2008 at 7:08 am
Michael,
thanks for that..... but it still wants me to put the column 'Date' in the group by statement.
If I use 'convert(varchar(10),Date,120)' in the select statement I don't get the time element returned?
JJ
May 14, 2008 at 7:41 am
Have a go at this. It will even find duplicate maximum concurrent users. Don't know how this will run with millions of records.
select s.Date,s.Application,m.maxusers
from
(
select convert(varchar(10),Date,120) d,Application,max(Concurrentusers) maxusers
from Sessions
group by convert(varchar(10),Date,120),Application
) m
join Sessions s on m.Application=s.Application and d=convert(varchar(10),s.Date,120) and m.maxusers=s.Concurrentusers
order by convert(varchar(10),s.Date,120),s.Application
May 14, 2008 at 7:47 am
May I cut in?
DECLARE@Sample TABLE (Date DATETIME, ConcurrentUsers INT, Application NVARCHAR(256))
INSERT@Sample
SELECT'20080514 15:15:14', 21, 'Peso' UNION ALL
SELECT'20080514 15:17:14', 11, 'Yak' UNION ALL
SELECT'20080515 15:10:10', 8, 'Peso'
SELECTDATEADD(DAY, DATEDIFF(DAY, '19000101', Date), '19000101') AS theDate,
MAX(Date) AS theTime,
COUNT(*) AS Users
FROM@Sample
GROUP BYDATEDIFF(DAY, '19000101', Date)
ORDER BYDATEDIFF(DAY, '19000101', Date)
N 56°04'39.16"
E 12°55'05.25"
May 14, 2008 at 7:53 am
Peso, your returns an error.
May 14, 2008 at 8:26 am
DECLARE@Sample TABLE (Date DATETIME, ConcurrentUsers INT, Application NVARCHAR(256))
INSERT@Sample
SELECT'20080514 11:00:00', 19, 'Peso' UNION ALL
SELECT'20080514 15:15:14', 21, 'Peso' UNION ALL
SELECT'20080514 15:17:14', 11, 'Yak' UNION ALL
SELECT'20080515 15:10:10', 8, 'Peso'
SELECTApplication,
theDate,
ConcurrentUsers,
Date
FROM(
SELECTDate,
DATEADD(DAY, DATEDIFF(DAY, '19000101', Date), '19000101') AS theDate,
ConcurrentUsers,
Application,
ROW_NUMBER() OVER (PARTITION BY Application, DATEDIFF(DAY, '19000101', Date) ORDER BY ConcurrentUsers DESC) AS RecID
FROM@Sample
) AS d
WHERERecID = 1
ORDER BYApplication,
theDate
N 56°04'39.16"
E 12°55'05.25"
May 15, 2008 at 2:22 am
Mike, Peso,
really big thank you guys! Both of your answers worked - but in slightly different ways.
I ran both against my data (1,126,066 records) and the results were as follows:
Peso - your query took 17.145 seconds and returned 7182 rows. The query returned the first instance of maximum concurrency occurring.
Mike - your query took 4.306 seconds and returned 15317 rows. The query returned all instances of maximum concurrency occurring.
Again, a big thanks you to you both.
With very kind regards
JJ
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply