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 DATEPART(month, DateTime) AS myMonth,
DATEPART(day, Datetime) AS myDay,
DATEPART(year, DateTime) AS myYear,
DATEPART(hour, DateTime) AS myHour,
COUNT(1) AS SentOk
FROM MacLog
WHERE DateTime BETWEEN <cfqueryparam value="#ARGUMENTS.StartDate#"
cfsqltype="cf_sql_timestamp" /> AND <cfqueryparam
value="#ARGUMENTS.EndDate#" cfsqltype="cf_sql_timestamp" />
AND ThinkTank_ID = <cfqueryparam
value="#ARGUMENTS.ThinkTankID#" cfsqltype="cf_sql_integer" maxlength="4" />
GROUP BY DATEPART(month, dateTime),
DATEPART(day, datetime),
DATEPART(year, dateTime),
DATEPART(hour, dateTime)
</cfquery>
http://80.244.184.135/teststats.cfm
Now that returns all the records and groups them by hour, and also displays
a count of the number of records found for each hour. Now I'm looking to
count the number of records in each hour that displays a particular value in
its LogClass_ID field, something like this.
COUNT(WHERE LogClass_ID = 1) AS Sent
COUNT(WHERE LogClass_ID = 2) AS Failed
COUNT(WHERE LogClass_ID = 3) AS Postponed
Or something to that effect. Then for each hour I'll know how many records
there are of each log class.
Any ideas?
Thanks,
Rob
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Macromedia ColdFusion MX7
Upgrade to MX7 & experience time-saving features, more productivity.
http://www.adobe.com/products/coldfusion?sdid=RVJW
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276614
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
