I need to add two columns created according to a date and grouping them by another column.
I need to create a new column called the previous balance that adds ( VALORDEBITO + VALORCREDITO
) and groups by CONTADEBITO
and CONTACREDITO
Query:
SELECT CODFILIAL,
CODLOTE,
CPARTIDA.CODCCUSTO,
GCCUSTO.NOME AS 'CENTRO DE CUSTO',
DATA,
DEBITO.CODCONTA CONTADEBITO,
CREDITO.CODCONTA CONTACREDITO,
DEBITO.DESCRICAO AS DESCRICAODEBITO,
CREDITO.DESCRICAO AS DESCRICAOCREDITO,
CASE
WHEN DEBITO.CODCONTA IS NOT NULL THEN CPARTIDA.VALOR
ELSE NULL
END AS VALORDEBITO,
CASE
WHEN CREDITO.CODCONTA IS NOT NULL THEN CPARTIDA.VALOR * -1
ELSE NULL
END AS VALORCREDITO,
IDPARTIDA,
COMPLEMENTO,
CHISTP.DESCRICAO AS 'HISTORICO'
FROM CPARTIDA (NOLOCK)
INNER JOIN GCCUSTO ON
GCCUSTO.CODCCUSTO = CPARTIDA.CODCCUSTO AND
GCCUSTO.CODCOLIGADA = CPARTIDA.CODCOLIGADA
INNER JOIN CHISTP ON
CHISTP.CODHISTP = CPARTIDA.CODHISTP AND
CHISTP.CODCOLIGADA = CPARTIDA.CODCOLIGADA
LEFT JOIN CCONTA CREDITO
ON CREDITO.CODCONTA = CPARTIDA.CREDITO AND
CREDITO.CODCOLIGADA = CPARTIDA.CODCOLIGADA
LEFT JOIN CCONTA DEBITO
ON DEBITO.CODCONTA = CPARTIDA.DEBITO AND
DEBITO.CODCOLIGADA = CPARTIDA.CODCOLIGADA
WHERE
DATA >='2018-01-01'
CODLOTE <> 0 AND
CPARTIDA.CODCOLIGADA =1