Home » SQL & PL/SQL » SQL & PL/SQL » how to add same account number debit n credits (Oracle 8i)
how to add same account number debit n credits [message #407185] |
Tue, 09 June 2009 00:02  |
snomi
Messages: 29 Registered: April 2008
|
Junior Member |
|
|
hi,
i have two tablespace year0708 and year0809 with same table structure, i apply eqi join to with two table (year0708.account and year0708.ledger) from tablespace year0708.
Follow is my query which i use
SELECT YEAR0708.LEDGER.CODE,YEAR0708.ACCOUNT.TITLE,
SUM(YEAR0708.LEGDER.DEBIT)AS DEBIT, SUM(YEAR0708.LEDGER.CREDIT) AS CREDIT
WHERE YEAR0708.LEDGER.CODE=YEAR0708.ACCOUNT.CODE AND
YEAR0708.LEGDER.VDATE>=:START_DATE AND YEAR0708.LEDGER.VDATE<=:END_DATE
GROUP BY YEAR0708.LEDGER.CODE,YEAR0708.ACCOUNT,TITLE
UNION ALL
SELECT YEAR0809.LEDGER.CODE,YEAR0809.ACCOUNT.TITLE,
SUM(YEAR0809.LEGDER.DEBIT)AS DEBIT, SUM(YEAR0809.LEDGER.CREDIT) AS CREDIT
WHERE YEAR0809.LEDGER.CODE=YEAR0809.ACCOUNT.CODE AND
YEAR0809.LEGDER.VDATE>=:START_DATE AND YEAR0809.LEDGER.VDATE<=:END_DATE
GROUP BY YEAR0809.LEDGER.CODE,YEAR0809.ACCOUNT,TITLE
following is output
CODE TITLE DEBIT CREDIT
--------------------------------------------------------
0902211 LIABILITIES 827,4333.23 0
0902211 LIABILITIES 927,4333.23 823,6477.00
0903311 ASSERTS 527,433.23 457,3843.33
0903311 ASSERTS 427,433.23 257,3843.33
I want to output like this
CODE TITLE DEBIT CREDIT BALANCE
-----------------------------------------------------------------------------
0902211 LIABILITIES 827,4333.23 0
0902211 LIABILITIES 927,4333.23 823,6477.00
-----------------------------------------------------------------------------
10,101,766.46 823,6477.00 865,319.56
-----------------------------------------------------------------------------
0903311 ASSERTS 527,433.23 457,3843.33
0903311 ASSERTS 427,433.23 257,3843.33
-----------------------------------------------------------------------------
954,866.46 257,3843.33 -1,618,976.87
means i want to add debit and credit values of same account number in each tablespace
any help will be appreciable
thanks
[Updated on: Tue, 09 June 2009 00:15] Report message to a moderator
|
|
|
|
Re: how to add same account number debit n credits [message #407198 is a reply to message #407194] |
Tue, 09 June 2009 01:16   |
snomi
Messages: 29 Registered: April 2008
|
Junior Member |
|
|
wat i did .... i added debit and credit column of same account and then generate third column by debit-credit=balance
like
DEBIT CREDIT BALANCE
-----------------------------------------------------------------------
827,4333.23 0
927,4333.23(+) 823,6477.00 (+)
-----------------------------------------------------------------------
10,101,766.46 (-) 823,6477.00 = 865,319.56
-----------------------------------------------------------------------
(+) ADD
(-) SUBTRACT
(=) EQUAL
i hope its clear u more wat i did
[Updated on: Tue, 09 June 2009 01:43] Report message to a moderator
|
|
|
|
|
Re: how to add same account number debit n credits [message #407227 is a reply to message #407221] |
Tue, 09 June 2009 03:50   |
karthick_arp
Messages: 13 Registered: February 2006 Location: hyderabad
|
Junior Member |
|
|
user ROLLUP
SQL> with t
2 as
3 (
4 select 0902211 code,'LIABILITIES' title, 8274333.23 debit,0 credit from dual union all
5 select 0902211,'LIABILITIES',9274333.23,8236477.00 from dual union all
6 select 0903311,'ASSERTS',527433.23,4573843.33 from dual union all
7 select 0903311,'ASSERTS',427433.23,2573843.33 from dual
8 )
9 select code, title, debit, credit, decode(gt,1,debit-credit,0) balance
10 from (
11 select code, title, sum(debit) debit, sum(credit) credit, grouping(title) gt
12 from (select row_number() over(partition by code, title order by code, title) rno, t.* from t)
13 group by rollup(code,title, rno)
14 having NOT(grouping(code)=0
15 and grouping(title) = 0
16 and grouping(rno) = 1)
17 and grouping(code)=0
18 )
19 /
CODE TITLE DEBIT CREDIT BALANCE
---------- ----------- ---------- ---------- ----------
902211 LIABILITIES 8274333.23 0 0
902211 LIABILITIES 9274333.23 8236477 0
902211 17548666.5 8236477 9312189.46
903311 ASSERTS 527433.23 4573843.33 0
903311 ASSERTS 427433.23 2573843.33 0
903311 954866.46 7147686.66 -6192820.2
6 rows selected.
|
|
|
Re: how to add same account number debit n credits [message #407241 is a reply to message #407227] |
Tue, 09 June 2009 04:18   |
snomi
Messages: 29 Registered: April 2008
|
Junior Member |
|
|
boss u did it nice work but its so confusing for me like i don't how i customize this query accounding to my table schema.
The sql query i used for extrating data is this :
SELECT YEAR0708.LEDGER.CODE,YEAR0708.ACCOUNT.TITLE,
SUM(YEAR0708.LEGDER.DEBIT)AS DEBIT, SUM(YEAR0708.LEDGER.CREDIT) AS CREDIT
FROM YEAR0708.LEDGER, YEAR0708.ACCOUNT
WHERE YEAR0708.LEDGER.CODE=YEAR0708.ACCOUNT.CODE AND
YEAR0708.LEGDER.VDATE>=:START_DATE AND YEAR0708.LEDGER.VDATE<=:END_DATE
GROUP BY YEAR0708.LEDGER.CODE,YEAR0708.ACCOUNT,TITLE
UNION ALL
SELECT YEAR0809.LEDGER.CODE,YEAR0809.ACCOUNT.TITLE,
SUM(YEAR0809.LEGDER.DEBIT)AS DEBIT, SUM(YEAR0809.LEDGER.CREDIT) AS CREDIT
FROM YEAR0809.LEDGER, YEAR0809.ACCOUNT
WHERE YEAR0809.LEDGER.CODE=YEAR0809.ACCOUNT.CODE AND
YEAR0809.LEGDER.VDATE>=:START_DATE AND YEAR0809.LEDGER.VDATE<=:END_DATE
GROUP BY YEAR0809.LEDGER.CODE,YEAR0809.ACCOUNT,TITLE
can u pls modify your query according to my table and table column name
thank u so much
|
|
|
Re: how to add same account number debit n credits [message #407243 is a reply to message #407241] |
Tue, 09 June 2009 04:24   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
snomi wrote on Tue, 09 June 2009 11:18 | can u pls modify your query according to my table and table column name
|
Make sure you also get his phone-number, so you can call him whenever there is a problem with YOUR code.
Are you seriously thinking about taking code into production that you don't understand? If you cannot get what was done in that query, either ask which part you don't get or hand back your assignment and tell you are not yet up to it.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 20:49:25 CST 2025
|