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
