Teplace a subselect with a join in a case expression
I have a SQL query with a case expression. The case expression has a subquery. I want to replace (delete) the subselect with a join. It is possible? How can I do it?
The query:
SELECT tablex.Abnumber,
CASE WHEN Abdate IS NOT NULL AND ISNULL(Abnumber,0) > 1 AND Abdate < (SELECT Abdate FROM t_tablex WHERE Annumber = @Annumber AND Abnumber = @Abnumber-1)
THEN 'bla bla'
ELSE '' END
FROM t_tablex AS tablex
WHERE (@Annumber IS NULL OR tablex.Annumber = @Annumber)
AND (@Abnumber IS NULL OR tablex.Abnumber = @Abnumber)