Challenging one

nightowl

This one is quite challenging to me ...

I have a table of persons, a table of activities and a table in between to store the registrations (just holding person_id, activity_id and time_of_registration). The query underneath counts the number of persons who registered for acitivity1 and activity2 (well, in fact of the types of activities, but that is info is not necessary).

However, now I would like to count "the number of persons who registered FIRST for activity1 and THEN for activity2 (so where time_of_registration1 < time_of_registration2). How could I do this ?

$sql = "select count(*) from persons
where person_id in
(select person_id
from registrations
where activity_id in
(select activity_id
from activities
where activitytype='$type1->activitytype_id' AND
fiscal_year='$cur_year'))
AND
person_id in (select person_id
from registrations
where activity_id in
(select activity_id
from activities
where activitytype='$type2->activitytype_id' AND
fiscal_year='$cur_year'))";

luckybob

Do your query on the database to pull the times for the registration on both activity 1 and 2 and then compare them. If you saved your time as an integer (ie time()) then compare the two.

<?php
$sql = ""; //sql statment
$result = mysql_query/COLOR;

while ( $myrow = mysql_fetch_array/COLOR) {

 [COLOR=royalblue]$activity1time[/COLOR] =  [COLOR=royalblue]$myrow[/COLOR][ [COLOR=purple]"Activity1time"[/COLOR]];
 [COLOR=royalblue]$activity2time[/COLOR] =  [COLOR=royalblue]$myrow[/COLOR][ [COLOR=purple]"Activity2time"[/COLOR]];

 [COLOR=blue]if[/COLOR] ( [COLOR=royalblue]$activity1time < $activity2time[/COLOR]) {
 [COLOR=blue]count[/COLOR]++;
 [COLOR=green]//Person signed up for activity 1 first add one to the count[/COLOR]
}

barand

SELECT Count(registration.person_id) AS CountOfperson_id
FROM registration
WHERE (((registration.activity_id)=1))
AND (((registration.regdate)<(select r.regdate from registration  r where 
registration.person_id = r.person_id and r.activity_id = 2)))

hth