That's good to hear. BTW, you may be able to simplify this a bit.
Place your larger query in the sub-select area, then the outside select would only do the outer join. SELECT HourOfDay, ReportDate FROM Hours LEFT OUTER JOIN ( SELECT ReportDate FROM MacLog WHERE... GROUP BY... ) AS TempMacLog ORDER BY ... The inner-most SELECT statement would do all the summarizing with the GROUP BY, then the outer SELECT would only need to add the hour of day value. M!ke -----Original Message----- From: Robert Rawlins - Think Blue [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 08, 2007 2:33 AM To: CF-Talk Subject: RE: Table Data Morning Mike, I think part of the problem was that the group by was cutting it down to 11 hours, so I worked with an SQL guy on creating a sub query, it took a few revisions but we finally got it up and running, it's a bit of a monster to say the least. SELECT Hour.HourOfDay, DATEPART(month, Hour.ReportDate) AS myMonth, DATEPART(day, Hour.ReportDate) AS myDay, DATEPART(year, Hour.ReportDate) AS myYear, DATEPART(hour, Hour.ReportDate) AS myHour, DATEPART(Weekday, Hour.ReportDate) As myWeekday, DATENAME(Weekday, Hour.ReportDate) As myWeekdayName, 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 ( SELECT DISTINCT Hour.HourOfDay, DateAdd(Day, DateDiff(Day, 0, MacLog.DateTime), 0) AS ReportDate FROM Hour INNER JOIN MacLog ON MacLog.DateTime BETWEEN <cfqueryparam value="#ARGUMENTS.StartDate#" cfsqltype="cf_sql_timestamp" /> AND <cfqueryparam value="#ARGUMENTS.EndDate#" cfsqltype="cf_sql_timestamp" /> ) AS Hour LEFT OUTER JOIN MacLog ON Hour.HourOfDay = DATEPART(hour, MacLog.DateTime) AND Hour.ReportDate = DateAdd(day, DateDiff(Day, 0, MacLog.DateTime), 0) AND 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, Hour.ReportDate), DATEPART(day, Hour.ReportDate), DATEPART(year, Hour.ReportDate), DATEPART(hour, Hour.ReportDate), DATEPART(Weekday, Hour.ReportDate), DATENAME(Weekday, Hour.ReportDate), Hour.HourOfDay ORDER BY DATEPART(month, Hour.ReportDate), DATEPART(day, Hour.ReportDate), DATEPART(year, Hour.ReportDate), Hour.HourOfDay That works a charm at the moment, I've still got a lot of testing to do with it, crunching the numbers by hand alongside to check the results, but it appears to be working just fine. Thanks for your initial ideas of the hours table and the LEFT OUTER JOIN, that got us off to a good footing when working on this. Cheers mate, Rob ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http://www.adobe.com/products/coldfusion/flex2/?sdid=RVJQ Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:277249 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4