select 
Albergo.Codalb,
Albergo.CodComune,
Albergo.Descrizione,
dataupdate=(SELECT MAX(CASE WHEN DataArrivo >= IsNull(DataPartenza,'99990101') THEN DataArrivo ELSE DataPartenza END) FROM dbo.Archivio_mov_new Where codalbergo=albergo.codalb and codcomalbergo=albergo.codcomune
and dataarrivo between '01-03-2010' and '31-03-2010' and IsNull(DataPartenza,'99990101') <= '31-03-2010')
From dbo.Albergo
group by Codalb,CodComune,Descrizione