Add two created columns

Question

I need to add two columns created according to a date and grouping by another column.

I need to create a new column called previous balance that disappears ( VALORDEBITO + VALORCREDITO) and group 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

Answer 1

CREATE TABLE #CPARTIDA
(
    CODFILIAL INT,
    CODCCUSTO INT,
    VALOR DECIMAL (18,2),
    IDPARTIDA INT,
    CODLOTE INT,
    CODHISTP INT,
    CREDITO INT,
    DEBITO INT,
    CODCOLIGADA INT,
    COMPLEMENTO VARCHAR(100),
    DATA DATETIME
)
GO

INSERT INTO #CPARTIDA VALUES (1, 1, 20, 1, 1, 1, 1, 1, 1, 'COMP 1', GETDATE())
INSERT INTO #CPARTIDA VALUES (2, 2, 50, 2, 2, 2, 2, 2, 2, 'COMP 2', GETDATE())
INSERT INTO #CPARTIDA VALUES (3, 3, 70, 3, 3, 3, 3, 3, 3, 'COMP 3', GETDATE())

CREATE TABLE #GCCUSTO
(
    CODCCUSTO INT,
    CODCOLIGADA INT,
    NOME VARCHAR(100)
)
GO

INSERT INTO #GCCUSTO VALUES (1, 1, 'NOME 1')
INSERT INTO #GCCUSTO VALUES (2, 2, 'NOME 2')
INSERT INTO #GCCUSTO VALUES (3, 2, 'NOME 3')

CREATE TABLE #CHISTP
(
    CODCOLIGADA INT,
    CODHISTP INT,
    DESCRICAO VARCHAR(100)
)
GO

INSERT INTO #CHISTP VALUES (1, 1, 'DESC 1')
INSERT INTO #CHISTP VALUES (2, 2, 'DESC 2')
INSERT INTO #CHISTP VALUES (3, 3, 'DESC 3')

CREATE TABLE #CCONTA
(
    CODCONTA INT,
    CODCOLIGADA INT,
    DESCRICAO VARCHAR(100)
)
GO

INSERT INTO #CCONTA VALUES (1, 1, 'DESC 1')
INSERT INTO #CCONTA VALUES (2, 2, 'DESC 2')
INSERT INTO #CCONTA VALUES (3, 3, 'DESC 3')


SELECT *, SUM(TABELA.VALORDEBITO + TABELA.VALORCREDITO) TOTAL FROM (
    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' AND CODLOTE <> 0 AND  #CPARTIDA.CODCOLIGADA =1

  ) TABELA
  GROUP BY  TABELA.[CENTRO DE CUSTO], TABELA.CONTACREDITO, TABELA.CONTADEBITO, TABELA.DESCRICAOCREDITO, TABELA.DESCRICAODEBITO, TABELA.HISTORICO, 
            TABELA.CODFILIAL, TABELA.CODLOTE, TABELA.CODCCUSTO, TABELA.DATA, TABELA.VALORDEBITO, TABELA.VALORCREDITO, TABELA.IDPARTIDA, TABELA.COMPLEMENTO

http://sqlfiddle.com/#! 18/c0e14/1