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
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
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
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
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
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
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
7 matches
Mail list logo