Do this with a single SQL

Answer 1

Warning: Not a DBA by any means. ;)

But, a quick, untested stab at it:

SELECT min(CLNDR_DATE) FROM [TABLE]
WHERE (EFFECTIVE_DATE IS NOT NULL)
  AND (CLNDR_DATE > (
    SELECT max(CLNDR_DATE) FROM [TABLE] WHERE EFFECTIVE_DATE IS NULL
  ))

Assuming you want the first CLNDR_DATE with EFFECTIVE_DATE after the last without.

If you want the first with after the first without, change the subquery to use min() instead of max().

Answer 2

The first result from this recordset is what you're looking for. Depending on your Database, you may be able to only return this row by using LIMIT, or TOP

SELECT CLNDR_DATE 
FROM TABLE
WHERE CLNDR_DATE > (SELECT MAX(CLNDR_DATE)
                    FROM TABLE 
                    WHERE EFFECTIVE_DATE IS NOT NULL)
ORDER BY CLNDR_DATE

Answer 3

When you are in Oracle environment you can use analytical functions (http://www.orafaq.com/node/55), which are very powerful tools to do kind of queries you asking for.

Using standard SQL I think it is impossible, but maybe some SQL gurus will show up some nice solution.

Answer 4

Cool, Looks like First answer should works.

Answer 5

Using Oracle's analytic function (untested)

select *
from
(
  select 
    clndr_date, 
    effective_date, 
    lag(clndr_date, 1, null) over (order by clndr_date desc) prev_clndr_date
  from table
)
where effective_date is null

The lag(clndr_date, 1, null) over (order by clndr_date desc) returns the previous clndr_date, or use null if this is the first row.

(edit: fixed order)