hi master
Sir my problem is I need cumulative balance in my ledger report
I have year opening balance in
accopening table
SQL> desc accbal;
Name Null? Type
----------------------------------------- -------- --------------
ACCID VARCHAR2(15)
CRBAL NUMBER
DRBAL NUMBER
This is data
ACCID DRBAL CRBAL
--------------- ---------- ----------
K1101 46291132
K1102 13182173
K1103 23784045 so more
And
Sir my transaction save in master and detail table
Voumaster is master voudetail is detail
SQL> desc voumaster;
Name Null? Type
----------------------------------------- -------- --------------------
VNO NOT NULL VARCHAR2(20)
ENTDATE DATE
DISCRIPTION VARCHAR2(240)
SQL> desc voudetail;
Name Null? Type
----------------------------------------- -------- ----------------------
VNO VARCHAR2(20)
ACCID VARCHAR2(15)
NARRATION VARCHAR2(240)
DEBIT NUMBER
CREDIT NUMBER
select voumaster.entdate,voudetail.vno,voudetail.accid,voudetail.debit,voudetail.credit
from voudetail,voumaster where
voumaster.vno=voudetail.vno;
this is my transaction data
ENTDATE VNO ACCID DEBIT CREDIT
--------- -------------------- --------------- ---------- ----------
02-JUL-05 BPV050701K K410903 1173
02-JUL-05 BPV050701K K130102 22293
04-JUL-05 BPV050703K K130103 3000
04-JUL-05 BPV050704K K22010302 3000
04-JUL-05 BPV050702K K22010302 4000
04-JUL-05 BPV050702K K130103 4000
04-JUL-05 BPV050703K K22010302 3000
04-JUL-05 BPV050704K K130103 3000
04-JUL-05 BPV050705K K130406 5000
04-JUL-05 BPV050705K K130103 5000
05-JUL-05 BPV050706K K230302 19000
Sir I need running balance
First my query get opening balance from accbla and check balance is dr or cr after this get all balance form transaction table be for given date and add given date dr or cr amount in running total
Such as
Select drbal,crbal from accbal where accid=?K13032? ;
Select sum(debit),sum(credit) from voudetail where date <?give date?;
And final query
Select accid ,debit,credit, runingbalance form voudetail where accid=?1222? and date between ?given date?
Sir crbal,drbal add only first column
Please sir give me idea how I get running total by using tow table
Thanking you
Aamir
how I get running total by using tow table
-
- Posts: 132
- Joined: Sun Apr 02, 2006 10:36 am
- Location: Pakistan
Assalam-o-Alaikum
Aamin please send me data (.dmp) if possible or SQL scritp at mirza_rehan@yahoo.com and please must c.c. to rehan.mirza@unionexp.com
so i will send you yours required reports
take care
Allah Hafiz
Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807
When ever you pray
Please remember me and my family
Aamin please send me data (.dmp) if possible or SQL scritp at mirza_rehan@yahoo.com and please must c.c. to rehan.mirza@unionexp.com
so i will send you yours required reports
take care
Allah Hafiz
Best regards,
R E H A N M I R Z A
System Analyst / Senior Oracle Developer
Cell: +92-304-2120807
When ever you pray
Please remember me and my family
Assalam-o-Alaikum
first i am not Aamin my name is aamir
brother i will remember you and your family
in my pray
brother in am sending yuo my dum data file
i need both type of ledger single and multy
when single then print simple with opbal and befor given date balance and runing total
when multy then give result same is group total when code change then pik opbal asper code form accbal table
brother in dum file your use
accbal for openbalance amount
chartofacc for title
voumaster for date and vno and otherdetail
voudetail for amount and accid
sir can you give me idea how i get record no in report
is i get row now then i use this code
if row=1 then
cp_1=opba+amount
else
cp_1=cp+cp_1+amount
end if
please give me idea
thank aamir
first i am not Aamin my name is aamir
brother i will remember you and your family
in my pray
brother in am sending yuo my dum data file
i need both type of ledger single and multy
when single then print simple with opbal and befor given date balance and runing total
when multy then give result same is group total when code change then pik opbal asper code form accbal table
brother in dum file your use
accbal for openbalance amount
chartofacc for title
voumaster for date and vno and otherdetail
voudetail for amount and accid
sir can you give me idea how i get record no in report
is i get row now then i use this code
if row=1 then
cp_1=opba+amount
else
cp_1=cp+cp_1+amount
end if
please give me idea
thank aamir
Who is online
Users browsing this forum: No registered users and 0 guests