Catch only the values that are different in the query

Question

Catch only the values that are different in the query

Navigation

#1 by (0 votes)

0

I made a query that brings the values of credit and debit, but I need to bring only the values that are different from these two fields. At the moment I do not know why he is bringing everyone. Look at the image: OBS IS IN SQL SERVER

sql code:

  declare @data date='2018-05-21'
  select * from (select sum (ContabLancValor) as debito,ContabLancNumCtrl, 
  CAST(ContabLancHistComp AS varchar(max)) as NomeCliente  from 
  CONTAB_LANCAMENTO 
  where ContabLancData >=@data
  and ContabLancData <=@data
  and ContabLancCtaCred is not null
  and EmpCod='01.02'
  group by CAST(ContabLancHistComp AS varchar(max)), ContabLancNumCtrl)debito
  inner join(

  select sum (ContabLancValor) as credito,ContabLancNumCtrl from 
  CONTAB_LANCAMENTO 
  where ContabLancData >=@data
  and ContabLancData <=@data
  and ContabLancCtaDeb is not null
  and EmpCod='01.02'
  group by CAST(ContabLancHistComp AS varchar(max)), ContabLancNumCtrl) credito 
  on credito.ContabLancNumCtrl=debito.ContabLancNumCtrl
  where debito.debito<>credito.credito

sql sql-server

asked by anonymous 02.07.2018 / 15:31

1 answer

PHP / Database, add MD5 and verify [duplicate] Lock to not show another div while one is already visible to the user

score 0 · Accepted Answer

Felipe, if I understood correctly what you need is to know which entries have divergence between the sum of debits and credits. If this is the case, check if the code below caters to you.

-- código #1 v2
;
with agrupaLanc as (
SELECT ContabLancNumCtrl, 
       sum(case when ContabLancCtaCred is not null then ContabLancValor else 0 end) as soma_debito,
       sum(case when ContabLancCtaDeb is not null then ContabLancValor else 0 end) as soma_credito
  from CONTAB_LANCAMENTO
  where ContabLancData = @data
        and EmpCod='01.02'
  group by ContabLancNumCtrl
)
SELECT A.ContabLancNumCtrl, A.soma_debito, A.soma_credito,
       L.ContabLancValor, L.ContabLancHistComp,
       L.ContabLancCtaCred, L.ContabLancCtaDeb
  from agrupaLanc as A
       inner join CONTAB_LANCAMENTO as L on L.ContabLancNumCtrl = A.ContabLancNumCtrl
  where A.soma_debito <> A.soma_credito;