Re: SQL to generate percentage growth per time period?

On Nov 28, 12:30 pm, DG problem <skatef..._at_gmail.com> wrote:
> Just wondering what is the easiest way to show percentage growth of
> database objects per time period.
>
> For example, what SQL would show the weekly percentage growth of the
> following data?
>
> alter session set nls_date_format= 'YYYYmmdd';
> create table t (time date,mb integer);
> insert into t values('20071117',103442);
> insert into t values('20071110',97592);
> insert into t values('20071103',93446);
> insert into t values('20071026',87648);
> insert into t values('20071020',84191);
> insert into t values('20071013',76495);
> insert into t values('20071006',72294);
> commit ;
> select * from t order by 1 desc ;
>
> Thanks

Found my answer

select
  trunc(time,'WW'),
  max(mb) mb,
  round((max(mb)-first_value(max(mb))
  over (order by trunc(time,'WW')
    rows 1 preceding))
    *100/first_value(max(mb))
    over (order by trunc(time,'WW')
    rows 1 preceding),1) "%"
from t
group by trunc(time,'WW')
ORDER BY trunc(time,'WW') desc; Received on Wed Nov 28 2007 - 23:52:32 CST