Home » SQL & PL/SQL » SQL & PL/SQL » Need help on the query
icon5.gif  Need help on the query [message #212847] Mon, 08 January 2007 09:29 Go to next message
mohapatra
Messages: 24
Registered: August 2006
Location: New Jersey
Junior Member
TABLE A

PK1_id
PK2
PK3
Code



TABLE B

PK1_id
seq_number
date
code_status



TABLE A

PK1_id PK2 PK3 code
123 23 435 150
123 23 210 150
223 23 436 150
223 23 213 150
...

Table B

PK1_id seq_number date code_status
123 1 10/10/2006 applied
123 2 12/10/2006 applied
123 3 13/10/2006 approved
223 1 10/12/2006 Applied
223 2 11/12/2006 approved



output sud have pk1_d , date1 (for those rows which has code_status = applied, and the date sud be minimum if more than 1)
and date2 (for those rows which has code_status=approved and the date sud be minimum if more than 1)
and sud select only those rows which has code_status as both applied and approved for A.code = 150.)


pk1_d date1 (applied) date2 (approved)
123 10/10/2006 13/10/2006
223 10/12/2006 11/12/2006
Re: Need help on the query [message #212865 is a reply to message #212847] Mon, 08 January 2007 11:32 Go to previous message
anilsinare
Messages: 22
Registered: December 2005
Location: ipswich, uk
Junior Member
See following query -

select
pk1_id,
min((select min(dt) from table_b b where a.PK1_ID = b.PK1_ID and upper(code_status) = 'APPLIED')) Applied_date,
min((select min(dt) from table_b b where a.PK1_ID = b.PK1_ID and upper(code_status) = 'APPROVED')) Approved_date
from table_a a
group by pk1_id;

You need to update it a little bit.
Previous Topic: DBMS_REFRESH
Next Topic: SQL QUERY to eliminate dups. and only vendors from current year
Goto Forum:
  


Current Time: Fri Dec 02 21:05:30 CST 2016

Total time taken to generate the page: 0.16315 seconds