Thanks for the help on that Mike, I'll toy around with simplifying it later today, I like to keep things as lightweight as possible. On a slightly different concept I've been trying to adapt that query to group my data into days (that's the easy part) and then display them as a week, and I want the query to always return 7 results, if there aren't any records for that day then just leave it at 0, whereas without any of this fancy LEFT OUTER stuff it'll just leave a gap.
I've posted about it on the SQL list but not had any bites yet, perhaps you could pass your eye over it and give me your thoughts. http://www.houseoffusion.com/groups/sql/thread.cfm/threadid:776 It's the same query as below but gives a bit more of an explanation of what I'm trying to do with it. Just for the record I'd also like to -THEN- make a version of the query that has the same resulting effect but for 12 months of the year :-D Thanks again pal, Rob -----Original Message----- From: Dawson, Michael [mailto:[EMAIL PROTECTED] Sent: 08 May 2007 13:05 To: CF-Talk Subject: RE: Table Data 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:277274 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
