If you are getting 0 records, then take it down to a basic query of your first five columns. This means drop any SUM() columns, WHERE clause, GROUP BY clause items. (I also use SQL Server.)
You should be left with only the SELECT, FROM and LEFT OUTER JOIN parts of your query. That simple query should then return some records. Then, start putting the rest of it back together one item at a time. The WHERE clause is limiting your results to 0 records. Your SUM() functions should still return a record with 0 values in the column, but that still means you got back, at least, one record. So, that's why I think if you remove the WHERE clause, it will help find the problem. It's possible the OUTER JOIN isn't working as I thought it would, but I don't see why not. Removing the WHERE clause will help find that problem as well. Good luck! -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Monday, May 07, 2007 6:52 AM To: CF-Talk Subject: RE: Table Data Afternoon Mike, Thanks for this info mate, sorry it's taken me a while to get back, I've been busy on other stuff. I've set down this morning and played around with this but I'm struggling a little bit with it. I've implemented it into one of my queries, but it just makes the query return 0 results. The problem lies in this multiple record stuff, as I DO have more than record per hour, sometimes a couple of hundred but they are grouped into hours in the query, so they effectively display as a single row using counts to get summed values. Take a look at the query (with your outer join on it), Perhaps seeing the code will help a little. I'm running MS SQL Server opposed to the MySQL that I think you use in your example, but hopefully the principles are very similar. SELECT Hour.HourOfDay, DATEPART(month, MacLog.DateTime) AS myMonth, DATEPART(day, MacLog.Datetime) AS myDay, DATEPART(year, MacLog.DateTime) AS myYear, DATEPART(hour, MacLog.DateTime) AS myHour, SUM(CASE WHEN MacLog.LogClass_ID = 1 THEN 1 ELSE 0 END) AS Success, SUM(CASE WHEN MacLog.LogClass_ID = 2 THEN 1 ELSE 0 END) AS Failed, SUM(CASE WHEN MacLog.LogClass_ID = 3 THEN 1 ELSE 0 END) AS NoAction, COUNT(DISTINCT MacLog.MacAddress_ID) AS UniqueDevices FROM Hour LEFT OUTER JOIN MacLog ON Hour.HourOfDay = DATEPART(hour, MacLog.DateTime) WHERE MacLog.DateTime BETWEEN <cfqueryparam value="#ARGUMENTS.StartDate#" cfsqltype="cf_sql_timestamp" /> AND <cfqueryparam value="#ARGUMENTS.EndDate#" cfsqltype="cf_sql_timestamp" /> AND MacLog.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, MacLog.DateTime), Hour.HourOfDay Thanks pal, Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Deploy Web Applications Quickly across the enterprise with ColdFusion MX7 & Flex 2 Free Trial http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277139 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
