Task 5(ships)

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 

select

s.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

order

by s.name

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s