How to avoid rerunning a subquery used multiple times?

Question

The following is a simplified example of a problem I'm running into. Assume I have a query "SomeQuery" (SELECT... FROM... WHERE...) that gives an output that looks like this:

Status	MyDate
A	6/14/2021
A	6/12/2021
B	6/10/2021
A	6/8/2021
B	6/6/2021
A	6/4/2021

I need to get the earliest status A date that is greater than the maximum status B date. In this case 6/12/2021.

I have a query that looks like this:

SELECT
  MIN(MyDate) AS DateNeeded
FROM
  SomeQuery
WHERE
  Status = 'A'
  AND MyDate > (
    SELECT
      MAX(MyDate) AS MaxDateB
    FROM
      SomeQuery
    WHERE
      Status = 'B'
  )

This works, but I would like to avoid running the subquery twice. I tried assigning an alias to the first instance of the subquery, and then using that alias in place of the second instance, but this results in an "Invalid object name" error.

Any help would be appreciated. Thanks.

Answer 1

but to avoid hitting table twice you could use window function:

select top(1) Mydate from 
(
 select *, max(case when Status = 'B' then Mydate end) over () MaxBDate from data 
) t 
where status = 'A'
and MyDate > MaxBDate
order by Mydate

db<>fiddle here