Home » SQL & PL/SQL » SQL & PL/SQL » Closing Balance Problem (Oracle 10g)
Closing Balance Problem [message #406867] Sat, 06 June 2009 22:21 Go to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
TAB1

ITEMDESC	TYPE		QTY	VALUE
ITEM1		RECEIPT		100	1000
ITEM1		RECEIPT		100	1100	
ITEM1		ISSUE		 50		
ITEM1		RECEIPT		100	1200	
ITEM1		ISSUE		 50	
ITEM1		ISSUE		 50		

HERE WHEN I RECEIVE THE ITEM I CAN RECEIVE WITH VALUE.
WHEN IT HAS TO BE ISSUED IT SHOULD BE CALCULATED BASED ON THE PREVIOUS CLOSING BALANCE.
WHENEVER WE MAKE AN ISSUE QTY AND VALUE SHOULD GET REDUCED AND WHENEVER WE MAKE AN RECEIPT QTY AND VALUE SHOULD GET ADDED

i.e.,
my result table should be as follows
ITEMDESC	TYPE		QTY	VALUE	 RATE 	CLQTY  	CLBAL
ITEM1		RECEIPT		100	1000     10.0	100	1000
ITEM1		RECEIPT		100	1100	 11.0	200	2100 		
ITEM1		ISSUE		 50	 275 	  5.5	150	1825
ITEM1		RECEIPT		100	1200	 12.0 	250	3025
ITEM1		ISSUE		 50	 605	 12.1	200 	2420
ITEM1		ISSUE		 50	 605	 12.1   150	1851

RATE IS CALCULATED BASED ON THE PREVIOUS CLBAL AND CLQTY I.E., CLBAL/CLQTY
AND ISSUE VALUE IS RATE*QTY

IS IT POSSIBLE TO ACHIEVE THIS THROUGH SQL

[EDITED by LF: applied [pre] tags]
  • Attachment: CLBAL.txt
    (Size: 0.89KB, Downloaded 86 times)

[Updated on: Sun, 07 June 2009 03:13] by Moderator

Report message to a moderator

Re: Closing Balance Problem [message #406868 is a reply to message #406867] Sat, 06 June 2009 22:30 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Too bad you choose to ignore Posting Guidelines
http://www.orafaq.com/forum/t/88153/0/
Re: Closing Balance Problem [message #406872 is a reply to message #406867] Sat, 06 June 2009 23:25 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The Posting Guidelines clearly state "Don't cross-post. Duplicates will be removed."

Re: Closing Balance Problem [message #406874 is a reply to message #406867] Sun, 07 June 2009 00:55 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The posting guidelines also clearly stated:
- don't post in upper case
- format your post
- align the columns
- post Oracle version with 4 decimals
- post a working test case: create table and insert statements
- ...

Regards
Michel
Previous Topic: Problem while getting a line
Next Topic: selelct from a field
Goto Forum:
  


Current Time: Fri Dec 09 02:20:56 CST 2016

Total time taken to generate the page: 0.16947 seconds