For each ship from Ships table, determine the name of the time-nearest battle from Battles table, in which the ship could participate after year of launch .
If year of launch is unknown, take the latest battle. If there is no battle which have occured after the ship was launched, deduce NULL instead of the name of battle.
Remark: Consider that the ship can participate in battle which has occured in the year of launch of the ship.
Result set: name of the ship, year of launch, name of battle
selects.name, s.launched, (select CASE WHEN (cast(’01/01/’ + cast(s.launched as char(4)) as datetime) > (SELECT TOP 1 date from Battles order by date desc)) THEN NULL WHEN s.launched IS NULL THEN (SELECT TOP 1 Battles.name from Battles order by date desc) ELSE (SELECT TOP 1 Battles.name from Battles where date >= cast(’01/01/’ + cast(s.launched as char(4)) as datetime) order by date) END battle
)battle from ships s