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
-----Original Message-----
From: Dawson, Michael [mailto:[EMAIL PROTECTED]
Sent: 07 May 2007 17:23
To: CF-Talk
Subject: RE: Table Data
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:277228
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
