subtracting two rows from the same column
548123Apr 4 2012 — edited Apr 4 2012Hi,
I have the following table with about 6000 rows of different year_month but I am compare and subtract v
Organization Year_month Contribution
Kano JAN-2011 200000
KADUNA JAN-2011 300000
ABUJA JAN-2011 400000
Kano FEB-2011 300000
KADUNA FEB-2011 200000
ABUJA FEB-2012 600000
I want to select a year_month at run time to subtract the contribution of the first year_month from the contribution of the second year_month and give me the result as shown in the following table
Organization JAN-2011 FEB-2011 diffrence
Kano 200000 300000 -100000
KADUNA 300000 200000 100000
ABUJA 400000 600000 -200000
Here is my code returning too many va
create or replace function "GET_MONTHLY_VALUE"
(q_name in VARCHAR2,
hmoCode in VARCHAR2)
return VARCHAR2
is
c1 number;
begin
select NHIS_CONTRIBUTION into c1 from CONTRIBUTION_MGT where upper(YEAR_MONTH)=upper(q_name) and upper(ORGANIZATION)=upper(hmoCode);
return c1;
exception when NO_DATA_FOUND THEN
return null;
end;
------a call to the above function:
create or replace function process_cont_monthly return varchar
is
Cursor cont_cursor is select ORGANIZATION from CONTRIBUTION_MGT;
begin
for
cont_rec in cont_cursor loop
select GET_MONTHLY_VALUE(:p26_month,cont_rec.ORGANIZATION) first, GET_MONTHLY_VALUE(:p26_month2,cont_rec.ORGANIZATION) second,
abs(GET_MONTHLY_VALUE(:p26_month,cont_rec.ORGANIZATION)-GET_MONTHLY_VALUE(:p26_month2,cont_rec.ORGANIZATION)) Diffrence
from CONTRIBUTION_MGT;
end loop;
commit;
end;
I became totally confused and don’t know what to do next
Any help and better guide is appreciated