Advanced SQL Question

[cf.Objective]

I'm working on a side project and need to use a SQL query to return aggregate
numbers for for date/time ranges, i.e. if I wanted to return the number of
times a particular CF template was hit during the course of 24 hours, by hour:

Assuming 1 24-hour period and the resource is index.cfm, I want to see:
12AM - 1AM, 200 hits
1AM-2AM, 23 hits
2AM-3AM, 99 hits

So for a 24-hour period, I need 24 rows returned by my query. I could do this
with a stored procedure or by issuing 24 separate queries, but I suspect it can
be done with one query. I just can't quite figure out how to structure the
query so I get the results I want.

Anyone have a suggestion?

Laterz!

[Kronin555]

What database? what's your table look like?

At a very high level, this is what you need to do:

select count(*), hour from access_table where
resource = 'index.cfm'
and day = '6/1/2005'
group by hour

It's more complex than that because I'm assuming hour is embedded in a time
field, which you'll have to do some processing on in order to group by a single
hour.

[CF_Oracle]

If you don't want stored procedure but store each hit with time in database you
could create reference table with 24 hours range records with 3 columns:
hour_id, from, to; then count number of hits group by hour_id using join this
table with the other one where hits related time.

[cf.Objective]

Hey Kronin,

Thanks... that's the basic structure I've been looking for. For some reason,
aggregate functions have always presented me with a challenege. That should get
me pointed in the right direction.

As far as your questions:
Database is SQLServer 2000 on Wink2k AS
Table structure is yet to be determined, but will probably look a lot like the
W3C standard log format with the addition of a site key column provided by the
system to the user, allowing me to create a log that supports any number of
sites and extract stats for any of them by joining the user, site, and log
tables.

Thanks, I appreciate it.

Laterz!

[cgsj_usa@yahoo.com]

The solution that I am about to describe is similar to the one that you are
about to employ, but doesn't require the use/creation of a table. Simply use
the case statement in MSSQL in the select clause and the group by clause. That
ought to solve your issue. Hope that this helps. Thanks.

Chris