Add two created columns

Asked

Viewed 453 times

0

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
  • You’re trying to make a column saldoanterior add the value of the previous line to make up the next, type bank statement ?

  • 1

    Give a numerical example please , for me at least not clear , also inform the DBMS in question because some solutions are unique.

1 answer

0

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

  • The fiddle is not working...

  • Andreia, it would be interesting to provide some kind of explanation with the code, until he understands that you simulated his environment and used fictitious values and what are the points that he should pay attention to. Another suggestion is quato to use Sqlfiddle do not know if it creates the temporary tables correctly. See this link (it’s your code so that you can simulate by clicking the run sql button) http://sqlfiddle.com/#! 18/0b5e3/2.

Browser other questions tagged

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