Q:
Need help with this Oracle Query
BPC_APPLICATIONS_TRANSACTIONS (Table)
Application_no,Transaction_no,Transaction_date Step_id Dept_code Decision
20090045 1 01/14/2009 10:07:17 1 001 0
20090045 2 01/14/2009 10:07:17 70 007 0
20090045 3 01/14/2009 10:08:57 1 001 0
20090045 4 01/14/2009 10:08:57 60 006 0
20090045 5 01/14/2009 10:14:12 71 007 1
20090045 6 01/14/2009 10:14:41 61 006 1
20090045 7 01/18/2009 11:45:13 73 007 1
20090045 8 01/18/2009 11:46:26 74 007 1
20090045 9 01/18/2009 11:47:13 7 001 1
20090045 11 01/18/2009 11:48:43 70 007 0
20090045 10 01/18/2009 11:48:43 1 001 0
20090045 12 01/27/2009 11:22:16 1 001 0
20090045 13 01/28/2009 10:30:56 71 007 1
20090045 14 01/28/2009 10:31:37 73 007 1
20090045 15 01/28/2009 10:32:14 74 007 1
20090045 16 01/28/2009 10:35:44 7 001 1
20090045 17 01/28/2009 10:35:53 1 001 0
20090045 18 01/28/2009 10:35:53 70 007 0
20090045 19 02/01/2009 10:35:53 71 007 0
-----------------------------------------------------------------------------------------------------
select a.application_no,a.transaction_date dateIn,b.transaction_date dateout,
a.dept_code,b.decision
from bpc_applications_transactions a, bpc_applications_transactions b
where a.dept_code=007 and a.step_id in (71,73) and b.step_id in (71,73) and a.transaction_date in
(select transaction_date from bpc_applications_transactions
where step_id=71 and a.application_no=b.application_no )
and b.transaction_date in (select transaction_date from bpc_applications_transactions
where step_id=73 and a.application_no=b.application_no )
and
b.decision in (select nvl(decision,0) from bpc_applications_transactions
where step_id=73 and a.application_no=b.application_no )
and a.application_no=b.application_no;
---------------------------------------------------------------------------------------------------------
When I am running the above query , i am getting the output below:
APPLICATION_NO DATEIN DATEOUT DECISION
20090045 1/28/2009 10:30:56 ص 1/18/2009 11:45:13 ص 1
20090045 1/28/2009 10:30:56 ص 1/28/2009 10:31:37 ص 1
20090045 1/14/2009 10:14:12 ص 1/28/2009 10:31:37 ص 1
20090045 1/14/2009 10:14:12 ص 1/18/2009 11:45:13 ص 1
20090045 02/01/2009 10:35:53 1/28/2009 10:31:37 ص 1
20090045 02/01/2009 10:35:53 1/18/2009 11:45:13 ص 1
----------------------------------------------------------------------------------------------------
But I should get the output as:
APPLICATION_NO DATEIN DATEOUT DECISION
20090045 1/28/2009 10:30:56 ص 1/28/2009 10:31:37 ص 1
20090045 1/14/2009 10:14:12 ص 1/18/2009 11:45:13 ص 1
20090045 02/01/2009 10:35 0
Can anyone help me to tune my query above to get the desired result?
Thanks,
SK...
Application_no,Transaction_no,Transaction_date Step_id Dept_code Decision
20090045 1 01/14/2009 10:07:17 1 001 0
20090045 2 01/14/2009 10:07:17 70 007 0
20090045 3 01/14/2009 10:08:57 1 001 0
20090045 4 01/14/2009 10:08:57 60 006 0
20090045 5 01/14/2009 10:14:12 71 007 1
20090045 6 01/14/2009 10:14:41 61 006 1
20090045 7 01/18/2009 11:45:13 73 007 1
20090045 8 01/18/2009 11:46:26 74 007 1
20090045 9 01/18/2009 11:47:13 7 001 1
20090045 11 01/18/2009 11:48:43 70 007 0
20090045 10 01/18/2009 11:48:43 1 001 0
20090045 12 01/27/2009 11:22:16 1 001 0
20090045 13 01/28/2009 10:30:56 71 007 1
20090045 14 01/28/2009 10:31:37 73 007 1
20090045 15 01/28/2009 10:32:14 74 007 1
20090045 16 01/28/2009 10:35:44 7 001 1
20090045 17 01/28/2009 10:35:53 1 001 0
20090045 18 01/28/2009 10:35:53 70 007 0
20090045 19 02/01/2009 10:35:53 71 007 0
-----------------------------------------------------------------------------------------------------
select a.application_no,a.transaction_date dateIn,b.transaction_date dateout,
a.dept_code,b.decision
from bpc_applications_transactions a, bpc_applications_transactions b
where a.dept_code=007 and a.step_id in (71,73) and b.step_id in (71,73) and a.transaction_date in
(select transaction_date from bpc_applications_transactions
where step_id=71 and a.application_no=b.application_no )
and b.transaction_date in (select transaction_date from bpc_applications_transactions
where step_id=73 and a.application_no=b.application_no )
and
b.decision in (select nvl(decision,0) from bpc_applications_transactions
where step_id=73 and a.application_no=b.application_no )
and a.application_no=b.application_no;
---------------------------------------------------------------------------------------------------------
When I am running the above query , i am getting the output below:
APPLICATION_NO DATEIN DATEOUT DECISION
20090045 1/28/2009 10:30:56 ص 1/18/2009 11:45:13 ص 1
20090045 1/28/2009 10:30:56 ص 1/28/2009 10:31:37 ص 1
20090045 1/14/2009 10:14:12 ص 1/28/2009 10:31:37 ص 1
20090045 1/14/2009 10:14:12 ص 1/18/2009 11:45:13 ص 1
20090045 02/01/2009 10:35:53 1/28/2009 10:31:37 ص 1
20090045 02/01/2009 10:35:53 1/18/2009 11:45:13 ص 1
----------------------------------------------------------------------------------------------------
But I should get the output as:
APPLICATION_NO DATEIN DATEOUT DECISION
20090045 1/28/2009 10:30:56 ص 1/28/2009 10:31:37 ص 1
20090045 1/14/2009 10:14:12 ص 1/18/2009 11:45:13 ص 1
20090045 02/01/2009 10:35 0
Can anyone help me to tune my query above to get the desired result?
Thanks,
SK...
ASKED:
Apr 5 2009 9:08 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
How exactly do you identify what is the OUT record for a given IN record ? what is the criteria ?
I'm guessing that a step_id=71 means IN, and a step_id=73 means OUT, and that for a given IN record, the corresponding OUT record is the one that has step_id=73 with the first transaction_no that is greater than its transaction_no.
I'm also guessing that a decision = 0 means that this IN record has no corresponding OUT record.
If I'm correct, a query like this could do the trick:
SELECT a.application_no,a.transaction_date DATE_IN, b.transaction_date DATE_OUT
FROM bpc_applications_transactions a, bpc_applications_transactions b
WHERE a.application_no = b.application_no
AND a.dept_code = 7
AND a.step_id = 71
AND b.step_id = 73
AND b.transaction_no = (SELECT MIN(transaction_no) FROM bpc_applications_transactions WHERE application_no = a.application_no AND step_id = 73 AND transacTion_no > a.transaction_no)
UNION
SELECT application_no,transaction_date,NULL
FROM bpc_applications_transactions
WHERE dept_code = 7
AND step_id = 71
AND decision = 0;
I currently don't have a database to test it, so please make the necessary corrections.
If this is not what you need, please provide more details.
