RE: Group By Hours

2007-05-01 Thread Gaulin, Mark
Check out datepart. -Original Message- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 01, 2007 7:42 AM To: CF-Talk Subject: Group By Hours 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

Re: Group By Hours

2007-05-01 Thread Pete Ruckelshaus
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

RE: Group By Hours

2007-05-01 Thread Robert Rawlins - Think Blue
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

RE: Group By Hours

2007-05-01 Thread Robert Rawlins - Think Blue
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

RE: Group By Hours

2007-05-01 Thread Robert Rawlins - Think Blue
Ok, So I now have this grouping as I want it too, using that date part function, I just need a little help with the counting. Look below for my current query and the results returned. !--- Query to Obtain Records --- cfquery name=LOCAL.qGetAllDateRangeForUnitByDay datasource= SELECT

Re: Group By Hours

2007-05-01 Thread Deanna Schneider
It's pretty much as you said, except the syntax is: SUM(CASE WHEN logclass_id = 1 THEN 1 ELSE 0 END) as sent On 5/1/07, Robert Rawlins - Think Blue wrote: Ok, COUNT(WHERE LogClass_ID = 1) AS Sent COUNT(WHERE LogClass_ID = 2) AS Failed COUNT(WHERE LogClass_ID = 3) AS Postponed

RE: Group By Hours

2007-05-01 Thread Robert Rawlins - Think Blue
Perfect Deanna, That works nicely, glad I've got you guys on hand, I would have NEVER gotten that one on my own. Thanks a million, Rob -Original Message- From: Deanna Schneider [mailto:[EMAIL PROTECTED] Sent: 01 May 2007 14:23 To: CF-Talk Subject: Re: Group By Hours It's pretty much