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;

Totti schrieb:
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

Totti schrieb:
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

Totti schrieb:
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

Totti schrieb:
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

Thank you Maxim, this did the job well;

Thank you Maxim, this did the job well;

Thank you Maxim, this did the job well;

Thank you Maxim, this did the job well;