Requirement is something like
------------------------------

oppty   d1              d2               d3                  d4                d5
-------------------------------------------------------------------------------------------
10      01-feb-2007    20-mar-2007       null             14-jun-2007         null
10      12-apr-2007    31-may-2007       07-jun-2007      null                15-oct-2007
10      null           13-jun-2007       15-jul-2007      17-sep-2007         31-aug-2007
-------------------------------------------------------------------------------------------

Passed date must be checked between the ranges of d1,d2,d3,d4
Say, for instance the passed date is 12-jun-2007 then the o/p would be

oppty        date
-----------------
10           d2  ( since the passed date is falling between d2 and d4 and d3 is null )
10           d3  ( since the passed date is falling between d3 and d5 and d4 is null )
10           d1  ( since the passed date is falling between d1 and d2 and d2 is being occupied with the greater value
                   compared to the passed date )