This is what the table looks like -
time id code
8/22/1999 12:00:00 AM 0001 A <------------ no
8/24/2001 6:03:02 AM 0001 A
6/27/2002 4:45:20 PM 0001 B
5/8/2003 9:03:13 AM 0001 B
5/8/2003 10:02:34 AM 0001 A <------------ no
6/9/2008 10:43:03 AM 0001 A
11/22/2011 3:42:10 PM 0001 A
4/11/2012 2:03:49 PM 0001 D
4/11/2012 2:04:00 PM 0001 D
12/6/2017 9:30:17 PM 0001 A <------------ yes
12/6/2017 9:30:17 PM 0001 A
12/6/2017 10:06:11 PM 0001 A
12/7/2017 3:24:58 AM 0001 C
1/3/2018 5:02:13 PM 0001 C
0001 is an ID from another table. CODE can flip from A to B to C to D any time and in any order. I need to find the most recent occurrence when the code flipped to A from any other code. In this example it happens in the row indicated with "yes". The "no" records are not valid because there is a later occurrence of this situation.
Can't figure out how to go about this. This probably needs a stored procedure, going record by record, maintaining state and figuring out the value.
Edit:
I was wondering if I could somehow add a column called grp like this -
id code
A 1
A 1
B 2
B 2
A 3
A 3
A 3
D 4
D 4
A 5
A 5
A 5
C 6
C 6
Then I would be able to get-
max of the min(grp) group by grp
Assuming this is Sybase ASE, though most of the following is fairly simple SQL which should convert easily into other RDBMS products
First we'll see if we can find the 3 rows where code changed to 'A'
select f1.[time],
f1.id,
f1.code
from fliptable f1
where f1.code = 'A'
and ( -- see if the previous record has a code!='A'; to find the 'previous'
-- record we find the row with max(time) < current record's time
exists (select 1
from fliptable f2
where f2.id = f1.id
and f2.code != f1.code
and f2.[time] = (select max(f3.[time])
from fliptable f3
where f3.id = f1.id
and f3.[time] < f1.[time]))
or
-- catch case where the 'first' row in the table has code='A'
not exists (select 1
from fliptable f4
where f4.id = f1.id
and f4.[time] < f1.[time])
)
order by f1.[time]
go
time id code
------------------------------- ---- ----
Aug 22 1999 12:00AM 0001 A
May 8 2003 10:02AM 0001 A
Dec 6 2017 9:30PM 0001 A
Dec 6 2017 9:30PM 0001 A <=== side effect of having a dup row in the data
From here we should be able to add a top 1 and flip to order by / desc to pull the last/newest record ...
select top 1
f1.[time],
f1.id,
f1.code
from fliptable f1
where f1.code = 'A'
and ( exists (select 1
from fliptable f2
where f2.id = f1.id
and f2.code != f1.code
and f2.[time] = (select max(f3.[time])
from fliptable f3
where f3.id = f1.id
and f3.[time] < f1.[time]))
or
not exists (select 1
from fliptable f4
where f4.id = f1.id
and f4.[time] < f1.[time])
)
order by f1.[time] desc
go
time id code
------------------------------- ---- ----
Dec 6 2017 9:30PM 0001 A
Tested on ASE 15.7 SP138