Re: analytic question

> How about
>
> select subject, last_date, result_avg
> from (select subject,
> test_date,
> max(test_date) over(partition by subject) last_date,
> avg(result) over(partition by subject
> order by test_date
> range between numtodsinterval(91, 'day') preceding
> and numtodsinterval(0, 'day') following) result_avg,
> count(decode(rn,1,1)) over(partition by subject
> order by test_date
> range between numtodsinterval(91, 'day') preceding
> and numtodsinterval(0, 'day') following) month_count
> from (select ct.*,
> row_number()
> over(partition by subject,trunc(test_date,'MM')
> order by test_date) rn
> from chemical_tests ct)
> ) inv
> where inv.test_date = inv.last_date
> and month_count >= 3
>
> ?

Seems to work well and fast, thank you.

        Y. Received on Wed Aug 18 2010 - 05:03:55 CDT