Home » SQL & PL/SQL » SQL & PL/SQL » Complex Logic using SQL (Oracle 10g)
| Complex Logic using SQL [message #601340] |
Tue, 19 November 2013 15:19  |
 |
achockal
Messages: 5
Registered: November 2013
|
Junior Member |
|
|
I have data as below in my table and I would like to select only the
rows based on below logic for each ID.
--> min(Status A)
--> next status B
--> first Status A after status B
--> next status B
Is there a solution available in SQL for above logic . Also there can be multiple A & B status values in my table for each ID.
ID STATUS ACTIVITY_DATE
--- ------- --------------
1 A 01/01/2010 *
1 A 01/02/2010
1 A 01/03/2010
1 B 01/03/2010 *
1 A 01/03/2010 *
1 A 01/04/2010
1 B 01/04/2010 *
Appreciate your inputs on the same.
|
|
|
|
|
|
|
|
| Re: Complex Logic using SQL [message #601343 is a reply to message #601340] |
Tue, 19 November 2013 16:02  |
|
achockal
Messages: 5
Registered: November 2013
|
Junior Member |
|
|
PFB the insert script for test data
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AR', TO_DATE('11/09/2013 13:52:46', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AP', TO_DATE('11/10/2013 13:53:02', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AR', TO_DATE('11/10/2013 13:53:11', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AR', TO_DATE('11/11/2013 13:53:23', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AR', TO_DATE('11/12/2013 13:53:31', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AP', TO_DATE('11/12/2013 13:53:40', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AR', TO_DATE('11/12/2013 13:53:48', 'MM/DD/YYYY HH24:MI:SS'), 200);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AR', TO_DATE('11/13/2013 13:54:00', 'MM/DD/YYYY HH24:MI:SS'), 200);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AP', TO_DATE('11/14/2013 13:54:17', 'MM/DD/YYYY HH24:MI:SS'), 200);
Insert into TEST_NOTE
(NOTE_TYPE, ACTIVITY_DATE, TASK_ID)
Values
('AR', TO_DATE('11/16/2013 13:54:47', 'MM/DD/YYYY HH24:MI:SS'), 200);
COMMIT;
|
|
|
|
|
|
|
|
| Re: Complex Logic using SQL [message #601360 is a reply to message #601349] |
Wed, 20 November 2013 00:56 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> select test_note.*,
2 case
3 when lag(note_type,1,' ') over (partition by task_id order by activity_date)
4 != note_type
5 then '*'
6 end flag
7 from test_note
8 order by task_id , activity_Date
9 /
NOTE_TYPE ACTIVITY_DATE TASK_ID F
---------- ------------------- ---------- -
AR 09/11/2013 13:52:46 100 *
AP 10/11/2013 13:53:02 100 *
AR 10/11/2013 13:53:11 100 *
AR 11/11/2013 13:53:23 100
AR 12/11/2013 13:53:31 100
AP 12/11/2013 13:53:40 100 *
AR 12/11/2013 13:53:48 200 *
AR 13/11/2013 13:54:00 200
AP 14/11/2013 13:54:17 200 *
AR 16/11/2013 13:54:47 200 * |
|
|
|
Goto Forum:
Current Time: Fri Apr 26 08:00:38 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
