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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

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 this case, I would like to subtract the value 545.00 minus the 480.00 and the result of this (65.00), compare with another table that I have called: cc_voice_voice compared to the column . And this way bringing me if the result (65.00) is different from the column "current_score".

Is it possible to do this?

Thank you in advance.

sql-server query

asked by anonymous 10.08.2017 / 23:42

3 answers

Save page settings when reloading PHP - Accessing CSV file and printing specific cells on the screen

score 1 · Answer 1

You can accumulate the results and display 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

score 0 · Answer 2

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'

score 0 · Answer 3

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 has worked, or you can only make the adjustment in your sql. So:

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')