sql - Reusing query/subquery results to assign multiple outputs depending on a column value -

i want assign 4 output values based on specific status of column, while counting how many occurrences of there are.

for example

   select @somevariable =(select count(*) r     table      join table 2 t2     on r.id= t2.id      getdate() between t2.start , t2.end ) 

adding statement such , t2.status="somestatus" works, way have to same query eachdifferent status have, possible reuse query or somehow sub query assignment of output variables based on t2.status

just in case code example bit messed (just writing memory), trying do, count how many columns there particular status , fits in time frame.

i've done need, having multiple queries in stored procedure, don't want keep way.

you can write query single select:

select @somevariable = count(*) table join      table 2 t2      on r.id = t2.id  getdate() between t2.start , t2.end; 

you can add more variables easily:

select @somevariable = count(*),        @somevariable2 = sum(case when t2.status = 'somestatus' 1 else 0 end) table join      table 2 t2      on r.id = t2.id  getdate() between t2.start , t2.end;