CODEWITHSUNDEEP
sqlsql-servert-sql
Aistis Taraskevicius
Aistis Taraskevicius

Reputation: 811

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

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

For example 

   Select @someVariable =(Select count(*) as r
    from table 
    join table 2 as t2
    on r.id= t2.id 
    where getdate() between t2.start and t2.end )

adding extra where statement such as and t2.status="somestatus" works, but this way I have to to same query for eachdifferent status I have, is it possible to reuse that query or somehow sub query assignment of output variables based on t2.status

Just in case my code example is bit messed up (just writing some from memory), what I am trying to do, is count how many columns there are with particular status and fits in time frame.

I've done what I need, by having multiple queries like this in stored procedure, but I don't want to keep it this way.

Upvotes: 1

Views: 184

Answers (2)

Luke101
Luke101

Reputation: 65248

Do you want something like this?

SELECT 
    @TotalCount = count(*), 
    @Status1 = s1.status1, 
    @Status2 = s2.status2, 
    @Status3 = s3.status3 
FROM [table]
CROSS JOIN (SELECT count(*) as status1 FROM [table] WHERE [status] = 'status1' and getdate() between [start] and [end]) s1
CROSS JOIN (SELECT count(*) as status2 FROM [table] WHERE [status] = 'status2' and getdate() between [start] and [end]) s2
CROSS JOIN (SELECT count(*) as status3 FROM [table] WHERE [status] = 'status3' and getdate() between [start] and [end]) s3

The results will output to variables using one query.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can write the query as a single select:

Select @someVariable = count(*)
from table join
     table 2 as t2
     on r.id = t2.id 
where getdate() between t2.start and t2.end;

You can then add more variables easily:

Select @someVariable = count(*),
       @someVariable2 = sum(case when t2.status = 'somestatus' then 1 else 0 end)
from table join
     table 2 as t2
     on r.id = t2.id 
where getdate() between t2.start and t2.end;

Upvotes: 3

Related Questions