select 
Albergo.Codalb,
Albergo.CodComune,
Albergo.Descrizione,
dataupdate=(SELECT MAX(CASE WHEN DataArrivo >= IsNull(DataPartenza,'19000101') THEN DataArrivo ELSE DataPartenza END) FROM dbo.Archivio_mov_new Where codalbergo=albergo.codalb and codcomalbergo=albergo.codcomune)
From dbo.Albergo
Left Join
dbo.Archivio_mov_new
On Albergo.Codalb=Archivio_mov_new.codalbergo and Albergo.CodComune=Archivio_mov_new.codcomalbergo
Group by codalb,codcomune,descrizione