Teplace a subselect with a join in a case expression
03:33 16 Apr 2019

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)
sql sql-server t-sql join subquery