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