Hello peoples i got a question . I havent been working alot with SQL queries and the like so please bear with me cause im slowly learning.

Anyhow i got a table that looks like this
date(date), time(TIME), name(varchar15), activity (int4).

Just to be clear 'activity' is the time a person spent on an activity in minutes.

The thing i want to achieven in a query is the following formula:
Activity = (1xaverage activity +3xaverage activity from yesterday + 2x average activity the day before yesterday) /4.6

average activity is calculated by the following:
Average activity = (MAX(activity) + MIN(activity)) /2, I have reasons to calculate it like this .

Anyhow im a bit stuck on how to do this. The parts i got so far is to calculate the average activity per day in a seperate query like this:
SELECT date, (MAX(activity) + MIN(activity)) /2 as avActivity from tabel group by DAY(date);

however i really dont know how to proceed further, as said eariler im a bit stuck. So if anyone has a good idea on how to go further thansk in advance

you'll need two self-joins, one to get yesterday and one for the day before yesterday

what happens if these don't exist? what is the formula if yesterday or the day before yesterday is missing?

Ah joins, havent been that far yet in the learning proces, ill look em up now thanks alot .

Ah and btw yesterday and the day before yesterday will ALWAYS exist. Mainly the query is being run from a php form where u can select from a dropdown the date. I already made it that the 'lowest date' that is selectable is the 3rd one in the table so that there is always a day before yesterday, and a day before

assume $dateparameter as a value like '2010-08-10'

Thanks alot sorry for late reply but ive been trying out to figure out the errors the query gave myself. Yours worked perfectly with the 1 exception :P

WHERE `date` = $dateparameter - INTERVAL 2 DAY ) AS daybefore
If i put the quotes around 'date' it doesnt work. Took me some time to figure out to remove em. After i did that it worked perfectly.

Thanks alot already. Now im trying to figure out how to do it for mulitple days but no succes in that yet :P. With multiple days. I could just run a loop in PHP but trying to do it in mysql :P

do you understand the syntactic difference between quotes and backticks?

to be honest i cant say i do (or did). Just looked it up and apparently back ticks should work, but i kept getting errors and when i removed em it seemed to work :P

assuming the column name is date,

WHERE `date` = ... will always work

WHERE 'date' = ... will never work

WHERE date = ... might work, but since DATE is a reserved word, might not