Higest login volume day / hour

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Higest login volume day / hour

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-06 : 12:34:47

The following are the history of login time for each person. I would like to find the highest log –in volume day / hour. For example, the return result should looks like:
The highest log in-volume day hour : Tuesday( 2004-03-30) 1:00 – 2:00 pm


Login-id    Login date_time

1              2004-03-29 13:12:55.500
2              2004-03-29 13:12:56.153
3              2004-03-30 13:13:04.500
4              2004-03-30 13:13:04.763
5              2004-03-30 13:13:22.403
6              2004-03-30 13:13:23.107
7              2004-03-30 13:13:23.170
8              2004-03-30 13:13:35.000
9              2004-03-30 13:13:35.233
10             2004-03-30 13:14:05.500
11             2004-03-30 13:14:06.000
12             2004-03-30 13:14:34.280
13             2004-03-30 14:14:34.543
14             2004-03-30 14:14:34.590
15             2004-03-31 13:14:40.217
16             2004-03-31 13:14:40.280
17             2004-03-31 13:14:53.840
18             2004-04-01 13:14:53.890
19             2004-04-01 13:15:08.013
20             2004-04-01 13:15:08.060

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-06 : 14:22:22

select TOP 1 convert(char(8), [Login Date_Time], 112), Hour([Login Date_Time]), count(*)
from LoginTable
GROUP BY convert(char(8), [Login Date_Time], 112), Hour([Login Date_Time])
ORDER BY 3 DESC

Duane.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-06 : 15:07:46

Thanks!

Is there a UDF/date format that convert from datetime to day?
For example, 2004/03/30 13:23:59 is Tuesday 1:00 pm

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-06 : 15:40:00

How's about this



USE Northwind
GO

CREATE FUNCTION udf_Hour(@Date datetime)
RETURNS varchar(50)
AS
  BEGIN
	RETURN 	CASE DATEPART(dw,@Date)
		WHEN 1 THEN 'Sunday'
		WHEN 2 THEN 'Monday'
		WHEN 3 THEN 'Tuesday'
		WHEN 4 THEN 'Wednesay'
		WHEN 5 THEN 'Thurday'
		WHEN 6 THEN 'Friday'
		WHEN 7 THEN 'Saturday'
		END
		+ ' ('
		+ CONVERT(varchar(8), @Date, 101) 
		+ ') ' 
		+ CONVERT(varchar(2),DATEPART(hh,@Date)) + ':00'
		+ ' - '
		+ CONVERT(varchar(2),DATEPART(hh,@Date)+1) + ':00'
  END
GO

SELECT dbo.udf_Hour(GetDate())
GO

DROP FUNCTION udf_Hour
GO

Brett

8-)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-06 : 17:50:26

Jung, "The highest log in-volume day hour : Tuesday( 2004-03-30) 1:00 – 2:00 pm" is a much better description of what you were trying to achieve. :) Glad someone could help you out.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources