Hello Guys,
I'm pretty poor when it comes to all this SQL stuff, but over the past
couple of days with the help of others I managed to place together a rather
complex query. It does exactly as its supposed too and I'm generally pretty
happy with it. I'm now looking to adapt this slightly for a different
application and was hoping someone here would be able to help. The initial
query takes a load of log records from a table called 'MacLog' and joins
then to a table called 'Hour'. The hour table contains 24 rows of a single
column which is an integer running from 0 to 23. This was so that when
viewing my records grouped by hour it would always return 24 records, and if
there wasn't any log data for particular hours, the values would be 0.
I'm now looking to take this very same concept, but group my log data into
'days' rather than hours, and display a week's worth at a time, if there was
a day missing, it just uses 0 values for that day rather than not returning
any row at all. I've started by creating another tally style table that is
called 'day' and runs from 1 to 7. But amending the query is a little beyond
my capability, I've tried a couple of times this morning with different
ideas, none of which seem to return anything that even resembles what I'm
looking for. Copied below is the query which works a charm for hours.
SELECT Hour.HourOfDay,
DATEPART(month, Hour.ReportDate) AS myMonth,
DATEPART(day, Hour.ReportDate) AS myDay,
DATEPART(year, Hour.ReportDate) AS myYear,
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,
SUM(CASE WHEN MacLog.LogClass_ID = 4 THEN 1 ELSE 0 END) AS
Failed1,
SUM(CASE WHEN MacLog.LogClass_ID = 5 THEN 1 ELSE 0 END) AS
Failed2,
SUM(CASE WHEN MacLog.LogClass_ID = 6 THEN 1 ELSE 0 END) AS
Appeared,
SUM(CASE WHEN MacLog.LogClass_ID = 7 THEN 1 ELSE 0 END) AS
Disappeard,
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
If anyone here is able to help me with this I would GREATLY appreciate it,
I'm hoping its nothing too complex to modify this to work with grouped days
instead of hours.
Thanks,
Rob
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:2841
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
