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 -----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: 04 May 2007 16:06 To: CF-Talk Subject: RE: Table Data Create a very simple table called "Hours" with an integer column named "HourOfDay". Then, insert 24 records such as: HourOfDay --------- 1 2 3 4 5 6 7 ..... 23 24 (You may need to use 0-23 instead of 1-24.) Then, OUTER JOIN the tables such as: SELECT Hours.HourOfDay, Articles.ArticleText FROM Hours LEFT OUTER JOIN Articles ON Hours.HourOfDay = HOUR(Articles.ArticleDate) WHERE Articles.ArticleDate = #cf_date_goes_here# ORDER BY Hours.HourOfDay You will always get all 24 records from the Hours table and any matching records from the article table, as long as you ONLY have one article per hour. The Hours table will "fill up" any gaps, however, it won't prevent any extra records. For example, if there were two articles posted between 9-10, then you would get 25 records. M!ke -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Friday, May 04, 2007 9:28 AM To: CF-Talk Subject: RE: Table Data Thanks Michael, That first solution sounds like a nice idea, would you be able to elaborate a little more? I'd much rather have my SQLServer do the work for me. Thanks, Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277134 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4