Hmmm. Well, I checked the HOUR() function and it returns 0-23 for the hours as I hoped.
I just tried a simple test and it worked for me. I did get multiple records for one hour, but I expected that from the test data I entered. I also got all other records, for each our in my Hours table. These were met with NULLs from the main table. Do you have all the hours (0-23) in your Hours table? Come to think of it, I bet your GROUP BY could be reducing the total number of hours down to 11. You may need to do it in a nested query or create a view for your first query (that sums and counts), then LEFT OUTER JOIN that view to the Hours table. M!ke -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Monday, May 07, 2007 8:46 AM To: CF-Talk Subject: RE: Table Data Thanks Michael, After playing around with this a lot today, it seems to defiantly be my GROUP BY clause that's causing the issue. The query isn't returning 0 results like I thought it was, I'd just picked a date which had no records (doh!) but now I've picked another date, it'll return 11 records (which is the number of hours that have records existing) which sort of brings us back round to square 1 again. I've tried stripping the query right back, so this should return just the hour datepart for the records, and group them by it, and its also got a WHERE clause to keep the records to a single day. SELECT Hour.HourOfDay, DATEPART(hour, MacLog.DateTime) AS myHour FROM Hour LEFT 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 Hour.HourOfDay, DATEPART(hour, MacLog.DateTime) Like I say, this returns 11 results, which is the same as if it didn't have the LEFT JOIN attached. Thanks for any further ideas, Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277154 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
