[lordmerlin]

Ok, this is quite a complicated one.

I got a table, which takes user comments, and ratings, when a user clicks a rating, it's being recorded, with the date.

I want to display these ratings as follows:

The total for the week:
total for the month:
total for the year:
total for rating 1
total for rating 2
total for rating 3
total for rating 4
total for rating 5

total for rating 1 for the week
total for rating 2 for the week
total for rating 3 for the week
total for rating 4 for the week
total for rating 5 for the week

total for rating 1 for the month
total for rating 2 for the month
total for rating 3 for the month
total for rating 4 for the month
total for rating 5 for the month

total for rating 1 for the year
total for rating 2 for the year
total for rating 3 for the year
total for rating 4 for the year
total for rating 5 for the year

Below is one of my select statements, showing the yearly total
Set yRS=Server.Createobject("ADODB.RECORDSET")
ySQL="SELECT COUNT(*) AS amount FROM rightclick_responses WHERE (datetime_added >= '" & YoldDate & "') AND (client_id = '" & Session
("companyid") & "') AND (questionaire_id = '2')"


This works, but I don't think 23 search expressions is a good idea, expesially not for performance.

Can anyone recommend something better?

[chemical]

1. Put all your SQL code inside a stored procedure. This way you only do a single hit to the database. Inside the stored procedure, create 23 variables, set their values through our SQL statements, and then return 1 row with 23 columns.

2. Make sure you index your table. You'll should probably create a clustered index on "companyid" and non-clustered indexes on "datetime_added" and "questionaire_id". Set your fill-factor correctly and re-build you indexes often.