Re: analytic question

> Why didn't you post the DDL and inserts to create the test data so
> someone could help you?

Sorry...

> SQL> select
> 2 subject,
> 3 last_date,
> 4 result_avg
> 5 from
> 6 (
> 7 select
> 8 subject,
> 9 test_date,
> 10 max(test_date) over (partition by subject) last_date,
> 11 avg(result) over (partition by subject order by test_date
> range
> 12 between numtodsinterval(91,'day') preceding and
> numtodsinterval(0,'day')
> 13 following) result_avg
> 14 from
> 15 testscores
> 16 ) inv
> 17 where
> 18 test_date >= (trunc(sysdate) - 91) and
> 19 inv.test_date = inv.last_date
> 20 /
>
> SUBJECT LAST_DATE RESULT_AVG
> ---------- --------- ----------
> 1 17-AUG-10 49
> 2 15-AUG-10 60.5
> 3 16-AUG-10 29.5
Sorry, my description of the requirement was wrong.

I have to include only the subjects that have at least 1 test in *each one* of the last 3 months ("last" is relative to the last test date available for the subject, non to sysdate).

For example: if the last test date of subject 01 is 2010-08-17, then 1 test in August, 3 in July, 2 in June is OK, 1 in August, 0 in July and 2  in June is not.

Thank you.

Kind regards, Y. Received on Tue Aug 17 2010 - 08:22:35 CDT