Home » SQL & PL/SQL » SQL & PL/SQL » Ledger summery
Ledger summery [message #439307] Fri, 15 January 2010 10:13 Go to next message
sharda.sanj@gmail.com
Messages: 3
Registered: January 2010
Junior Member
hello dear friends,

i want to create a sql from a table which the field r give blow
Doc_date date,
dr_amt number(10,2)
cr_amt number(10,2)

i want to outout as

Doc_date,open_balance,Dr_total,Cr_total,Closing_balance

can anyone help me
thx sanj
Re: Ledger summery [message #439308 is a reply to message #439307] Fri, 15 January 2010 10:14 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
And how do you calculate open_balance and Closing_balance?
Re: Ledger summery [message #439310 is a reply to message #439307] Fri, 15 January 2010 10:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sum(credit-debit) over(order by thedate)

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

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


Regards
Michel

[Updated on: Fri, 15 January 2010 10:17]

Report message to a moderator

Re: Ledger summery [message #439312 is a reply to message #439308] Fri, 15 January 2010 10:24 Go to previous messageGo to next message
sharda.sanj@gmail.com
Messages: 3
Registered: January 2010
Junior Member
my dear i was try to use this and i m fail to create opening bal


SELECT mytable.Clear_date,
NVL (LAG (op) OVER (ORDER BY clear_date), 0) opening,
mytable.dr_amount,
mytable.cr_amount,
mytable.op closing
from ( SELECT Clear_date,
sum(SUM(v_bank_ledger.dr_amount-v_bank_ledger.cr_amount)) OVER (ORDER BY clear_date) OP,
SUM (v_bank_ledger.dr_amount) dr_amount,
SUM (v_bank_ledger.cr_amount) cr_amount
FROM v_bank_ledger
GROUP BY v_bank_ledger.clear_date
) mytable
Where mytable.clear_date > '01-jan-07'
order by 1
Re: Ledger summery [message #439313 is a reply to message #439312] Fri, 15 January 2010 10:25 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>Where mytable.clear_date > '01-jan-07'

In Oracle characters between single quote marks are STRINGS
'this is a string, 2009-10-02, not a Date'
When you need to convert STRING to DATE use TO_DATE() function.
Re: Ledger summery [message #439315 is a reply to message #439313] Fri, 15 January 2010 10:30 Go to previous messageGo to next message
sharda.sanj@gmail.com
Messages: 3
Registered: January 2010
Junior Member
my dear friend that will work. and i got my date but only the i was not get opening value for the 1st record
Re: Ledger summery [message #439317 is a reply to message #439315] Fri, 15 January 2010 10:43 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>only the i was not get opening value for the 1st record
If you say so.

Without DDL for tables & DML for test data, it is a challenge to provide working SQL solution.

Re: Ledger summery [message #439321 is a reply to message #439312] Fri, 15 January 2010 11:12 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my dear friend that will work. and i got my date but only the i was not get opening value for the 1st record


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


By the way, the opening value for the first record is 0 (from what you posted).

Regards
Michel
Previous Topic: one-to-one with a join table
Next Topic: Ext Table
Goto Forum:
  


Current Time: Fri Dec 02 16:32:02 CST 2016

Total time taken to generate the page: 0.14102 seconds