For loop in query - how can i use Union? hello friends I ´m working on a module that graphs data from a database, the thing is that I must graph a series of data that went through a mathematical formula first. So far no problem, but I must take data from the months of a year, therefore I must repeat this process twelve times. My truth question is this:


  How can i get just one row an column per response?


If I try something like this ...

DECLARE @cnt INT = 0;
WHILE @cnt &lt; 12 
BEGIN
select idestatus from order 
SET @cnt = @cnt + 1;
END;


I'll get 12 queries executed &amp; 12 tables And i just need one. 
I will eventually transform this into a json, so I would like to have everything in a single response table
.This my query:

declare
@a decimal(5,2) = 0,
@d decimal(5,2) = 0,
@f1 decimal(5,2) = 0,
@f2 decimal(5,2) = 0

set
@a = (
select 
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set
@d = (
COUNT(idEstatus) A_D 
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)
set @f1 =
(
select
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 2
)
set @f2 =
(
COUNT(idEstatus) A_D
from t1
where (year(dat)) = 2017 and (month(dat)) = 1
and idEstatus = 3
)

select
 (((@a + @d)/2) *100)/((@f1 + @f2)/ 2) rf


If also some can tell me how to order this by a UNION , that could be really usefull to.
You can simplify your query above with this example, but it suffers from some limitations caused by our lack of knowledge of the schema of your table as well as what your final objective really is.  

This should do in one step what your query does, but it has hard coded idEstatus values (which may be ok) and is limited to one month.

It's not clear form your example query if you really need the second half since it appears to be the same date (if you wanted year over year then the correct answer is different).  

 ;WITH CTE_Details AS
    (
    SELECT YEAR(dat) AS [year]
        , MONTH(dat) AS [month]
        , idEstatus_Count_2 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 2 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
        , idEstatus_Count_3 = CAST(COALESCE(SUM(CASE WHEN idEstatus = 3 THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2))
    FROM t1
    WHERE idEstats IN (2,3)
        AND dat BETWEEN CAST('1/1/2017' AS DATETIME) AND CAST('1/31/2018 11:59:59 PM' AS DATETIME)
    GROUP BY YEAR(dat)
        , MONTH(dat)
    )
SELECT [year]
    , [month]
    , idEstatus_Count_2
    , idEstatus_Count_3
    , rf = (((idEstatus_Count_2 + idEstatus_Count_3) / 2.0) * 100) / ((idEstatus_Count_2 + idEstatus_Count_3) / 2.0)
FROM CTE_Details