I have a history table, and I want to select the first date occurrence
of a type 'A' event after the last event of type other than 'A', if
there is one. The best I can come up with is below. This seems
convoluted, especially compared with a cursor over the history in
descending order. Is this the best I can do in sql? Would a UDF using a
cursor, returning the chosen date be faster or more effective? How do I
extend this to work in the case the are no type<>'A' events? To make the
task more challenging, the type='A' phrase is significantly more complex
and this is just one column in a select over a 13-way join.

select startdate as first_contiguous
from history
where type='A'
and startdate=
(select min(startdate)
from history
where type='A'
and startdate>=
(select max(startdate)
from history
where type<>'A'))

How about:

with last_non_a(startdate) as (select max(startdate) from history where
type != 'A'),
recent_events(start_date, rownum) as (select startdate, row_number()
over (order by startdate asc) from history h, last_non_a l where h.type
= 'A' and h.startdate > l.startdate)
select startdate form recent_events where rownum = 1

I guess that isn't really any less convoluted, but it might possibly be
quicker.

-Chris

ChrisC wrote:

Brian Tkatch wrote:

the technique many times, just never in sql. Thank you.