Home » SQL & PL/SQL » SQL & PL/SQL » Pls help me to write an interesting SQL query (Oralce)
Pls help me to write an interesting SQL query [message #399993] Fri, 24 April 2009 19:25 Go to next message
liquidcrystal
Messages: 2
Registered: April 2009
Junior Member
Hi All,
I have a scenario...

Table A - current table with 3 fields
no dt balance

Table A_hist history table with 3 fields
no dt balance

Table B account code table with 3 fields
no dt cd

Table A is current table which holds 1 record for account with the account closed date

No Dt Balance
10 4/23/2009 0
20 4/24/2009 0
30 4/24/2009 0

Table A_hist is history table with account balance changes with dates
No Dt Balance
10 4/1/2009 2345
20 3/2/2009 1009
30 2/1/2009 879
30 4/22/2009 2500
20 1/31/2009 16000
20 4/15/2009 50
20 3/27/2009 18000
20 2/2/2009 111
10 2/27/2009 654
10 4/19/2009 2000

Table B is a table which holds account codes history
No Dt Cd
10 4/1/2009 aa
20 3/2/2009 bb
30 2/1/2009 cc
30 4/22/2009 cc1
20 1/31/2009 bb1
20 4/15/2009 bb2
20 3/27/2009 bb3
20 2/2/2009 bb4
10 2/27/2009 aa1
10 4/19/2009 aa2
20 4/24/2009 bbfinal
10 4/23/2009 aafinal
30 4/24/2009 ccfinal


Now I want account number with closing date, balance from before closing date and codes from closing date
No DT bal cd
10 4/23/2009 2000 aafinal
20 4/24/2009 50 bbfinal
30 4/24/2009 2500 ccfinal

Please help me out...Many thanks in advance
Re: Pls help me to write an interesting SQL query [message #399994 is a reply to message #399993] Fri, 24 April 2009 19:37 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.


Please realize that we do not do homework assignments.

[Updated on: Fri, 24 April 2009 19:38]

Report message to a moderator

Re: Pls help me to write an interesting SQL query [message #400017 is a reply to message #399993] Sat, 25 April 2009 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a working Test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Regards
Michel
Re: Pls help me to write an interesting SQL query [message #400037 is a reply to message #399993] Sat, 25 April 2009 05:56 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
So simple.
Join these tables using common field that is 'no'.
Use max(dt) from A_hist to get closing balance.

regards,
Delna
Previous Topic: DBMS_SESSION.SET_ROLE error (merged 3)
Next Topic: virtual columns
Goto Forum:
  


Current Time: Sat Dec 03 01:10:50 CST 2016

Total time taken to generate the page: 0.12325 seconds