--- Mag Gam <magawake@gmail.com> wrote:
> Hi All,
>
> I have a view that generates output similar to this.
>
> select * from foo.view;
>
> ts | size
> -------------------+-----
> 2002-03-16 | 11
> 2002-03-17 | 16
> 2002-03-18 | 18
> 2002-03-19 | 12
>
> I am trying to find the difference between the size column. So the
> desired
> output would be
>
> ts | size| Diff
> -------------------+-----+------
> 2002-03-16 | 11 | 0
> 2002-03-17 | 15 | 4
> 2002-03-18 | 18 | 3
> 2002-03-19 | 12 | -6
>
>
> I need the first column to be 0, since it will be 11-11. The second
> colum is
> 15-11. The third column is 18-15. The fourth column is 12-18.
>
> Any thoughts about this?
>
select cur.ts, cur.size, cur.size - coalesce( (select size from view next where next.ts = cur.ts - '1
day'::interval), cur.size ) as diff
from view cur;
alternately:
select cur.ts, cur.size, case when cur.ts = '2002-03-16' then 0 else cur.size - coalesce( (select size from view
next where next.ts = cur.ts - '1 day'::interval), cur.size ) end as diff
from view;
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ