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
