I am trying to build a query that has something like this
select id, (select top 1 create_date from table2 where table1id = t1.id and status = 'success') [last_success_date], (select count(*) from table2 where table1id = t1.id and create_date > [last_success_date]) [failures_since_success] from table1 t1
As you can see, the
[last_Success_Date] is not within the scope of the second query. I was wondering how could I access that value in other queries without having to rerun it?