Re: recursive formula help please!
Date: Sun, 13 Jan 2008 00:40:22 +0100
Message-ID: <47894FE6.6030309@gmail.com>
Totti schrieb:
> hi all, i have a sales table that i want to get the balance for some
> specific condition products,
> i am getting the wrong balance since i lack the knowledge of how to do
> a recursive formula, you may know that the balance of FEB for instance
> depends on JAN so on and so forth so my best attempt to solve the
> problem was the following, but it is still wrong, can anybody please
> help me correct it?
>
> select TO_CHAR(purinv_1.Inv_Date,'mm') || ' - ' ||
> TO_CHAR(purinv_1.Inv_Date,'yy') as "MONTH",
> sum(purch_1.qty*purch_1.unit_price) "Total Purchases",
> sum(sales_1.qty*prod_1.unit_price) "Total Sales",
> (sum(purch_1.qty*purch_1.unit_price))-
> (sum(sales_1.qty*prod_1.unit_price))as "Balance"
> from purch_1, sales_1, prod_1, salinv_1,purinv_1
> where prod_1.code=sales_1.prod_code
> and salinv_1.code = sales_1.inv_code
> and salinv_1.cust_code='Cu-30.1'
> group by TO_CHAR(purinv_1.Inv_Date,'mm'),
> TO_CHAR(purinv_1.Inv_Date,'yy')
> order by TO_CHAR(purinv_1.Inv_Date,'yy')
>
> this makes only a monthly balance but this is not what i want, i want
> to tie it to the present balance, or to the sum of previous balances;
>
> Thanks in advance for any help;
select to_char(trunc(purinv_1.inv_date, 'mm'),'mm"-"yy') as "MONTH",
sum(purch_1.qty * purch_1.unit_price) "Total Purchases",
sum(sales_1.qty * prod_1.unit_price) "Total Sales",
(sum(purch_1.qty * purch_1.unit_price)) - sum(sales_1.qty *
prod_1.unit_price)) as "Balance",
sum((sum(purch_1.qty * purch_1.unit_price)) - sum(sales_1.qty *
prod_1.unit_price)))
over(order by trunc(purinv_1.inv_date, 'mm')) as "Cumulated Balance"
from purch_1, sales_1, prod_1, salinv_1, purinv_1
where prod_1.code = sales_1.prod_code
and salinv_1.code = sales_1.inv_code and salinv_1.cust_code = 'Cu-30.1'
group by trunc(purinv_1.inv_date, 'mm') order by trunc(purinv_1.inv_date, 'mm')
Best regards
Maxim Received on Sat Jan 12 2008 - 17:40:22 CST