Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Recursive Reference in SQL Calc
I think you want
count pending this month = initial count + sum(count initiated this month - count closed this month) over (partition by month order by months rows unbounded preceding)
"damorgan" <damorgan_at_exesolutions.com> wrote in message
news:3C921745.D6B91DF3_at_exesolutions.com...
> Set up each of your subamounts as an inline view. Then query that.
>
> SELECT SUM(a + b - c)
> FROM
> (SQL query) a,
> (SQL query) b,
> (SQL query) c;
>
> Daniel Morgan
>
>
> Ian Jones wrote:
>
> > given the following table structure
> >
> > id,...,date_initiated, date_closed,...
> >
> > how would one do the following calculation for a date range, grouped
> > by month/year for charting purposes:
> >
> > count pending this month =
> > count pending last month +
> > count initiated this month -
> > count closed this month
> >
> > assuming the first month's count pending is:
> >
> > count initiated first month - count closed first month
> >
> > Thanks!!
> > Ian
>
Received on Fri Mar 15 2002 - 10:08:53 CST