Grouping by time??? - SQL Server Forums

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

SQL Server Development (2000)

Grouping by time???

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-19 : 10:21:33

I would like to write a query to return counts of web user activity in 10 minute intervals. There's a Column 'LoginDate' of datetime type in table users.

I can't see any way to do this 10 minute counting without generating a temporary table from starttime to stoptime with 10 minute interval rows containing each row's 10 minute start and stop times. The two tables could be joined, and counts grouped for each row.

Is this the hard way or is there an easier way with without using a temporary table?

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-19 : 10:25:19

SELECT DatePart(hour, LoginDate), (DatePart(minute, LoginDate)/10)*10, Count(*)
FROM myTable
GROUP BY DatePart(hour, LoginDate), (DatePart(minute, LoginDate)/10)*10

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-09-19 : 10:26:35

you could do intervals....as 12:00->12:09, 12:10->12:19, 12:20->12:29, etc...
in which case you are grouping upon the hour and the tens portion of the minutes....

convert/cast the date to a formatted string, and group by a substring of formatted string....

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-19 : 12:36:42

Rob's technique works pretty well, but introduces another nuance.

This query orders the histogram nicely

SELECT DATEPART(year, LastLogin) as [Year], DatePart(dayofyear, LastLogin) as [Day], DatePart(hour, LastLogin) as Hour, (DatePart(minute, LastLogin)/10)*10 as Minute, Count(*) as Total
FROM Users
where RegDate IS NOT NULL AND RegDate BETWEEN @startdate AND @enddate
GROUP BY DATEPART(year, LastLogin), DatePart(dayofyear, LastLogin), DatePart(hour, LastLogin), (DatePart(minute, LastLogin)/10)*10
order by DATEPART(year, LastLogin), DatePart(dayofyear, LastLogin), DatePart(hour, LastLogin), (DatePart(minute, LastLogin)/10)*10

But the day of year is between 1 and 365 now. Is there a simpler way to convert the dayofyear to a form like May 5, without first casting to datetime, then converting format?

SamC

SamC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-19 : 12:48:35

You can use DateAdd() and add the day of year to January 1, but you'll have to at least cast Jan 1 as a datetime. No biggie.

Don't be afraid to use Convert(), you can simplify the query with it:

SELECT Convert(datetime, Convert(varchar(8), LastLogin, 112)) AS TheDate, DatePart(hour, LastLogin) as Hour, (DatePart(minute, LastLogin)/10)*10 as Minute, Count(*) as Total
FROM Users
where RegDate IS NOT NULL AND RegDate BETWEEN @startdate AND @enddate
GROUP BY Convert(datetime, Convert(varchar(8), LastLogin, 112)), DatePart(hour, LastLogin), (DatePart(minute, LastLogin)/10)*10
order by TheDate, DatePart(hour, LastLogin), (DatePart(minute, LastLogin)/10)*10

And I'm positive Arnold will have an even easier method for calculating the date value without time that will shorten this even more.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-09-19 : 13:01:34

DATEDIFF
DATEADD
Probably. Sorry, brain's a bit fried today from writing XSLT programs that generate XSLT.

SamC
White Water Yakist

3467 Posts

Posted - 2002-09-19 : 13:34:31

Final solution:

SELECT DATEADD(hh, DatePart(hour, LastLogin), Convert(datetime, Convert(varchar(8), LastLogin, 112))) as [Date],
Count(*) as Total
FROM Users
where RegDate IS NOT NULL AND RegDate BETWEEN @startdate AND @enddate
GROUP BY DATEADD(hh, DatePart(hour, LastLogin), Convert(datetime, Convert(varchar(8), LastLogin, 112)))
ORDER BY [Date]

SamC

Edited by - SamC on 09/19/2002 14:04:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources