1

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 < 12 
BEGIN
select idestatus from order 
SET @cnt = @cnt + 1;
END;

I'll get 12 queries executed & 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.

  • What's the difference between @d and @f2? – Lennart May 8 '18 at 16:16
  • And between @a and @f1, for that matter? – RDFozz May 8 '18 at 16:32
  • As written, you could just write " SELECT 100 rf " assuming you don't care that you will never get a divide by zero error. – Keeta May 8 '18 at 18:57
5

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
  • Ty this really help me out just one there is one thing that causes me concern. My query and yours , they have a little significant difference of decimals. D: – E.Rawrdríguez.Ophanim May 8 '18 at 17:24
-1

It sounds like you need a group by

    select 
    datepart(year, dat) as year, datepart(month, dat), COUNT(idEstatus) A_D
    from t1
    where idEstatus = 2
    group by datepart(year, dat), datepart(month, dat)
  • i 'm not pretty sure to understand – E.Rawrdríguez.Ophanim May 8 '18 at 15:40
  • 1
    This hints at a solution to the OP's question, but isn't clear as to how to get the full answer from there - it would be better with more detail, and a tested solution to the problem (if possible). – RDFozz May 8 '18 at 16:09
  • @RDFozz he can literally paste the query into SSMS and get the output he is looking for. – James May 8 '18 at 16:18
  • 1
    @James - I don't think so ,as he's looking for a formula based on counts of rows where idEstatus = 2 and counts where idEstatus = 3. – RDFozz May 8 '18 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.