select logid, classid, datetime, datepart(year, datetime) + '/' + datepart(month, datetime) + '/' + datepart(day, datetime) + '/' + datepart(hour, datetime) AS groupbyvalue from table order by datetime
That looks sort of weird, but I think what you'll need to do is make sure you're grouping by more than just the hour. Otherwise, in your grouped output, you would have all of that hour for as many different month/day/year combos as are in your database (unless, that is, you're passing in a single date) Pete On 5/1/07, Robert Rawlins - Think Blue <[EMAIL PROTECTED]> wrote: > > Hello Guys, > > > > I have a bunch of records of log data, all with a datetime stamp on it, > and > I'm looking to have SQL Server return them grouped into hours. I've done > this before using MySQL I think, using the Hour() function in the query > but > it would seem that SQL Server doesn't support such a function. The table > looks something like this. > > > > LogID ClassID DateTime > > 1 1 01/01/2007 00:01:25 > > 2 1 01/01/2007 00:01:27 > > 3 1 01/01/2007 00:01:28 > > 4 1 01/01/2007 00:01:28 > > 5 2 01/01/2007 00:01:30 > > 6 1 01/01/2007 00:02:01 > > 7 2 01/01/2007 00:02:25 > > 8 1 01/01/2007 00:03:40 > > 9 2 01/01/2007 00:03:55 > > > > Any idea on the best way to do this? There could be tones of records per > hour you see, and it makes it much simpler to table and chart if they're > grouped into hours. I'd also like to perform a count for the number of > each > type if 'class' there was for each hour, so when outputting the data it > looks something like this. > > > > Date/Time Class 1 Class 2 > > 01/01/2007 00 6 3 > > > > Make sense? > > > > Thanks for any help guys, > > > > Rob > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276603 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
