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 Go to next message
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 #407194 is a reply to message #407185] Tue, 09 June 2009 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you explain where these added lines come from? How to you calculate them?
If I understand that 527,433.23 + 427,433.23 = 954,866.46, I hardly understand 10,101,766.46. Same thing for other columns above all BALANCE one.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: how to add same account number debit n credits [message #407198 is a reply to message #407194] Tue, 09 June 2009 01:16 Go to previous messageGo to next message
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 #407210 is a reply to message #407198] Tue, 09 June 2009 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where does 10,101,766.46 comes from (8274333.23 + 9274333.23 = 17548666.46)?
And for me 10101766.46-8236477.00 = 1865289.46 not 865,319.56

By the way, what is 823,6477.00 with a comma in this position? Same thing for 827,4333.23.

And we still have no test case.

Regards
Michel
Re: how to add same account number debit n credits [message #407221 is a reply to message #407210] Tue, 09 June 2009 03:08 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
oh yes you are rite its 1865289.46 its typo mistake, secondly i don't know how to create test case over here.
Re: how to add same account number debit n credits [message #407227 is a reply to message #407221] Tue, 09 June 2009 03:50 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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.
Re: how to add same account number debit n credits [message #407248 is a reply to message #407243] Tue, 09 June 2009 04:51 Go to previous messageGo to next message
snomi
Messages: 29
Registered: April 2008
Junior Member
well, if i not understand the code how could i embbed into my code, and their is no way i back off.
If non of person on this forum not help me its ok .. no probem .. i m still work around and i will get the way.
Re: how to add same account number debit n credits [message #407249 is a reply to message #407241] Tue, 09 June 2009 04:54 Go to previous messageGo to next message
karthick_arp
Messages: 13
Registered: February 2006
Location: hyderabad
Junior Member
Then you must spend some quality time with the document. You know its all really in there.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/aggreg.htm#sthref1612

Once you are done reading it you wont be confused much, believe me on this.

Thanks,
Karthick.
Re: how to add same account number debit n credits [message #407361 is a reply to message #407249] Tue, 09 June 2009 21:46 Go to previous messageGo to next message
alamtapash@gmail.com
Messages: 8
Registered: June 2009
Location: DHAKA
Junior Member
Good document.
Re: how to add same account number debit n credits [message #407412 is a reply to message #407361] Wed, 10 June 2009 02:18 Go to previous message
snomi
Messages: 29
Registered: April 2008
Junior Member
Thanks, karthick its really informative but i did it by using left grouping.
Previous Topic: Fill the Blank Values with Data (merged 3)
Next Topic: Writing EXECUTE IMMEDIATE
Goto Forum:
  


Current Time: Fri Dec 09 19:43:00 CST 2016

Total time taken to generate the page: 0.28959 seconds