How to Add Dependent Variable into Where Clause?

[Francis Chiu]

I want to do some average score calculations based on 30 days from client enrollment date, and 60 and 90.

Client's attempt to do a test is stored in testscore table like this:
clientname eventid attemptdate score
A 1001 2011-10-01 100
B 1002 2011-10-02 100
A 1003 2011-11-01 90
C 1004 2011-11-08 100
D 1005 2011-11-10 90
B 1006 2011-11-13 80
A 1007 2012-01-01 100
B 1008 2012-02-01 100

And client's enrollment details on:
clientname enrollmentdate
A 2011-10-01
B 2011-10-02
C 2011-11-08
D 2011-11-10

And I want to get the average scores of each client
from enrollmentdate - 30days after the start date
30 days after the start date - 60 days after the start date
60 days after the start date - 90 days after the start date

[Rabbit]

Join to the enrollment table to get the enrollment date. Then you can use an aggregate query to get the average for 30, 60, and 90 days. Either in three separate queries or in one query using different CASE statements to return NULL for the scores that fall out of the range.

[Francis Chiu]

It does not work... I drafted the query below and a temporary table for time frame X to Y.

select
c.clientname,
round(avg(CONVERT(float,t.score)),5,0)
from testscore t
join client c
on c.clientname=t.clientname
where t.attemptdate >= X
t.attemptdate < Y
group by clientname

#timeframe
client name X Y
A 2010-10-01 2012-11-01
B ...
C ...
D ...

How can I make it work?