Don't Worry, Onto something now with that date part stuff.
I'll let you know how I get on. Rob -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: 01 May 2007 13:48 To: CF-Talk Subject: RE: Group By Hours Thanks for the suggestion Pete, I'm sure that this datepart() function is defiantly going to be the way to do this, however I'm struggling to get your solution to work. When I try to run that query I get the following error. http://80.244.184.135/teststats.cfm Any ideas? I've not seen that '+ / + datepart' type thing used before, can you explain a little more about what that's actually doing? Is that just building a dynamic date without the minutes and seconds? Thanks, Rob -----Original Message----- From: Pete Ruckelshaus [mailto:[EMAIL PROTECTED] Sent: 01 May 2007 13:26 To: CF-Talk Subject: Re: Group By Hours 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 > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276611 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
