| retrieving previous status code [message #628147] |
Thu, 20 November 2014 07:40  |
 |
silver_fox
Messages: 1
Registered: November 2014
Location: Canada
|
Junior Member |
|
|
I am trying to retrieve the previous status code that is different from the current. The last 2 records may have the same status code but the previous one is different.
tried using decode with a lag, it shows in the prev_case_status_code column but i want it to be the only record displayed and the example below shows it grabbing the previous status_code, i need it to grab the previous status _code when there is a possibility of there being the same current status_code in the last 2 records. I am probably not using the right code.......help!!
SELECT t.case_id,t.end_date,t.case_status_code, decode(t.case_id,lag(t.case_id,1,0) over (order by t.case_id,
t.end_date), lag(t.case_status_code,1,0) over (order by t.case_id, t.end_date),0) as prev_case_status_code
from DMICM.TV_CASE_STATUS t
where t.case_id = 1042113
ORDER BY t.end_date
case_id end_date case_status_code previous status code
1 1042113 14/05/2008 1649 0
2 1042113 06/06/2008 1558 1649
3 1042113 06/06/2008 1216 1558
4 1042113 31/10/2008 1649 1216
5 1042113 16/01/2009 1558 1649
6 1042113 16/01/2009 1216 1558
7 1042113 31/10/2009 1649 1216
8 1042113 26/11/2009 1558 1649
9 1042113 26/11/2009 1216 1558
10 1042113 06/07/2010 1649 1216
11 1042113 04/08/2010 1505 1649
12 1042113 06/06/2011 1558 1505
13 1042113 06/06/2011 1216 1558
14 1042113 30/11/2011 1649 1216
15 1042113 05/12/2011 1558 1649
16 1042113 05/12/2011 1216 1558
17 1042113 31/12/4712 1649 1216
[Updated on: Thu, 20 November 2014 09:09] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
