How to calculate two columns and compare the result with another column?

Asked

Viewed 2,314 times

2

I have a table, where I am bringing two different results with the query below:

select sum(qtde_pontos) from dbo.tbl_cc_ponto where re = '9289'
    and natureza_operacao = '2' 
    and data_lacto >= '2012-01-01'
    and data_lacto <= '2017-08-10'
    union
select sum(qtde_pontos) from dbo.tbl_cc_ponto where re = '9289'
    and natureza_operacao = '1' 
    and data_lacto >= '2012-01-01'
    and data_lacto <= '2017-08-10'

The output is as follows::

480.00
545.00

In case, I would like to subtract the value 545.00 less the 480.00 and the result thereof (65.00), compare with another table I have called: cc_point comparing to the column current point. And this way bringing me if the result (65.00) is different or not from the column "ponto_saldo_actual".

It is possible to do this?

Thanks in advance.

database sql-server query

  • If you are using only Sqlserver or have a language in the middle of it all, or you want to create a Storedprocedure and do this, the options are several, as will compare with the other table?

  • At first I would just like to do this in SQL to see the result. Later, I would take it to PHP. But first I wanted to know if it’s possible in SQL. On the comparison, my question is two: bring the result of the two select and compare this value with another table.

  • With storedprocedure I believe it solves

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

3 answers

1

You can accumulate the results and show them as follows:

WITH calculo (montante) AS (
  SELECT (SELECT SUM(qtde_pontos)
            FROM dbo.tbl_cc_ponto
           WHERE re = '9289'
             AND natureza_operacao = '1' 
             AND data_lacto >= '2012-01-01'
             AND data_lacto <= '2017-08-10') -
         (SELECT SUM(qtde_pontos)
            FROM dbo.tbl_cc_ponto
           WHERE re = '9289'
             AND natureza_operacao = '2' 
             AND data_lacto >= '2012-01-01'
             AND data_lacto <= '2017-08-10') AS montante
)
SELECT CASE
         WHEN (SELECT c.montante
                 FROM calculo c) <> cpsv THEN 'DIFERENTE'
         ELSE 'IGUAL'
       END AS resultado,
       cpsv.*
  FROM cc_ponto_saldo_vecto cpsv

  • I think this one would work, but it is giving an error: Invalid column name 'cpsv'.

  • I’m sorry my failure, I didn’t understand the query. Now that I stopped to analyze block by block, I understood that I was falantaod put the comparison column. Thank you!

0

Try this query:

select 
sum(natureza_operacao = '1' ) as operacao1,
sum(natureza_operacao = '2' ) as operacao2,
(sum(natureza_operacao = '1' ) - sum(natureza_operacao = '2' )) as resultado
 from dbo.tbl_cc_ponto 
 where re = '9289'
    and data_lacto >= '2012-01-01'
    and data_lacto <= '2017-08-10'

0

SELECT (
         SUM(CASE WHEN natureza_operacao = '1' THEN qtde_pontos ELSE 0 END)  
          -
         SUM(CASE WHEN natureza_operacao = '2' THEN qtde_pontos ELSE 0 END) 
       ) as resultado
    from dbo.tbl_cc_ponto 
    where re = '9289'
     and data_lacto between '2012-01-01' AND '2017-08-10'
    GROUP BY natureza_operacao

I believe this worked, or you can just make the adjustment in your sql. Thus:

SELECT 
 (select sum(qtde_pontos) from dbo.tbl_cc_ponto where re = '9289'
  and natureza_operacao = '1' 
  and data_lacto >= '2012-01-01' and data_lacto <= '2017-08-10')
-
(select sum(qtde_pontos) from dbo.tbl_cc_ponto where re = '9289'
    and natureza_operacao = '2' 
    and data_lacto >= '2012-01-01' and data_lacto <= '2017-08-10')

Browser other questions tagged database sql-server query

You are not signed in. Login or sign up in order to post.