Produce calculated fields

[gullguten]

Hello folks. I'm new in this forum, but I have not found my question answered anywhere here so I guess it's OK for me to post it.

I have the following case.
Table [articleviews]
av_id
av_ip
av_datetime

This table records every presentation of any articles in my system.
The field [av_datetime] holds the date and time like yyyymmddhhmmss (14 chars)

What I would like to is to give in the current date/time (yyyymmddhhmmss) and retrieve the av_id (articleid) and two calculated fields. I want these calculated fields to be a counter for number of views for each [av_id] since (current datetime - 24 hours) and (current datetime - 24*7 hours).

example result:
.
..
...
av_id, av_countday, av_countweek
534, 45, 243
535, 34, 121
536, 56, 276
537, 12, 345
538, 19, 98
539, 23, 152
540, 31, 198
...

The av_countday and av_countweek should contain the count for each article for the last 24 hours and the last 24*7 hours.

I have solved this the easy way by using one record to hold the articles and the loop trough the postset and calculate (by SQL) for each of them. But I would like to do this by one single SQL-statement.

Any ideas ?

Thanx alot in advance.

[Banfa]

Hmmm I can think of a way of doing it with 2 SQL statements

for last 24 hours

SELECT av_id, COUNT(av_datetime) AS av_countday FROM articleviews WHERE DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= av_datetime;

for last week

SELECT av_id, COUNT(av_datetime) AS av_countday FROM articleviews WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= av_datetime;

[gullguten]

Thanks for answering my question.

After I posted my question I spent 'bout 30 minutes to implement a SP to solve my problem. And I have used your approach in my current version like this:
Create temporary table
Insert all articleIDs
Insert count for last 24 hours for give articleID
Insert count for last week for given articleID

Select-statement to return a recordset to the ASP-page

I have gained a real boost regarding the time for returning the result in the page, but I have a feeling that this problem should be possible to solve with just one single SQL-statement.

[CaptainD]

In answer to your question I created this to get personnel ID numbers from a table using two different date ranges and it works, see if you can adapt it to yours.

select count(*) from tblPersonnel where PersonnelID IN (SELECT personnelID from tblPersonnel where 
dateHired between DateAdd(year, -2, GetDate()) and GetDate())or PersonnelID IN 
(SELECT personnelID from tblPersonnel where dateHired between DateAdd(year, -5, GetDate()) and GetDate())