none
Criar coluna que calcule dados de duas outras colunas

    Question

  • Pessoal, bom dia!

    Necessito de uma ajuda em uma consulta SQL. Tenho o seguinte script:

     

     

    SELECT   FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.PAGAR_REC, FLMV.NATUR_MOV, FLMV.VR_BRUTO_MV
    FROM    FLMV INNER JOIN
          FLNT ON FLMV.EMPRESA = FLNT.EMPRESA AND FLMV.FILIAL = FLNT.FILIAL AND FLMV.NATUR_MOV = FLNT.NATUR_MOV AND 
          FLMV.NATUR_CONTA = FLNT.NATUR_CONTA AND FLMV.NATUR_TRANSACAO = FLNT.NATUR_TRANSACAO
    where FLMV.DT_MOVTO between '2010-07-01' and '2010-07-07' and flmv.natur_mov = 'admb'
    ORDER BY FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.NATUR_MOV, FLMV.NATUR_TRANSACAO<br/>
    <br/>
    <br/>
    
    

     

    Que me retorna o seguinte:

    DT_MOVTO                  NUMERO   PAGAR_REC     NATUR_MOV       VR_BRUTO_MV
    2010-07-01 00:00:00    1             R                     ADMB                 59000.0000
    2010-07-01 00:00:00    3             P                     ADMB                 1000.0000
    2010-07-01 00:00:00    4             R                     ADMB                 8500.0000
    2010-07-01 00:00:00    4             P                     ADMB                 3000.0000
    2010-07-07 00:00:00    3             P                     ADMB                 120.0000
    2010-07-07 00:00:00    4             P                     ADMB                  265.0300
    2010-07-07 00:00:00    5             P                     ADMB                  96.0000
    2010-07-07 00:00:00    6             P                    ADMB                  48.0000
    2010-07-07 00:00:00    7             P                    ADMB                  69.2500
    2010-07-07 00:00:00    8             P                    ADMB                  1000.0000

    Gostaria de criar três colunas que me retornassem o seguinte:

    PAGAR_REC   VR_BRUTO_MV  DÉBITO     CRÉDITO       ACUMULADO

    R                   59.000,00                          59.000,00      59.000,00

    P                     1.000,00          1.000,00                        58.000,00

    R                     8.500,00                            8.500,00      66.500,00

    P                     3.000,00          3.000,00                        63.500,00

    P                        120,00             120,00                        63.380,00

    P                        265,03             265,03                        63.114,97

    P                         96,00                96,00                        63.018,97

     

    As colunas que preciso criar são débito, crédito e saldo acumulado. Na coluna saldo acumulado o valor inicial deve ser o da coluna VR_BRUTO_MV. Quando a coluna PAGAR_REC for igual a R (de recebimento) o valor deve iniciar positivo, caso contrário negativo.

    Essa coluna deve ir calculando os valores das colunas débito e crédito, somando ou subtraindo.

     

    Estou usando SQL 2005.

    Obrigada.

    Êmili.

     

     

     

     

     

     

     

     

    Monday, August 02, 2010 1:34 PM

Answers

  • Então... vamos por partes....

    Não tem como usar tabela temporaria em View... vc a usa so para fazer um processamento momentario... no caso a ideia seria melhor entao criar uma Function

    CREATE FUNCTION TESTE ()
    RETURNS 
    @RESULT TABLE (TIPO CHAR(1), VALOR DECIMAL, DEBITO DECIMAL, CREDITO DECIMAL, TOTAL DECIMAL)
    AS
    BEGIN
    		
    	-- Declaração de Variaveis
    	DECLARE @TIPO AS CHAR
    	DECLARE @VALOR AS DECIMAL
    	DECLARE @DEBITO AS DECIMAL
    	DECLARE @CREDITO AS DECIMAL
    	DECLARE @TOTAL AS DECIMAL
    
    	-- Declaração do Cursos usado para ler linha a linha (algo como um DataReader do C#)
    	DECLARE C_SOMA CURSOR FOR 
    		SELECT 
    			PAGAR_REC, VR_BRUTO_MV, 
    			CASE WHEN PAGAR_REC = 'P' THEN VR_BRUTO_MV * -1 ELSE 0 END AS DEBITO, 
    			CASE WHEN PAGAR_REC = 'R' THEN VR_BRUTO_MV ELSE 0 END AS CREDITO 
    		FROM 
    			FLMV
    		
    	OPEN C_SOMA
    
    	SELECT @TOTAL = 0
    
    	-- Aqui é como o comando Read() do DataReader, le uma linha da query declada no Cursor e carrega os valores nas variaveis que estao apos o "INTO"
    	FETCH NEXT FROM C_SOMA INTO @TIPO, @VALOR, @DEBITO, @CREDITO;
    
    	-- Aqui vai fazer um While enquanto tiver registros na Tabela do Cursor
    	WHILE @@FETCH_STATUS = 0 BEGIN
    
    	-- Atualiza a variavel @TOTAL com o Valor do Saldo Total Atual
    		SELECT @TOTAL = @TOTAL + (@DEBITO + @CREDITO)
    
    	-- Insere o registro de movimentação na tabela temporaria
    		INSERT INTO @RESULT SELECT @TIPO, @VALOR, @DEBITO, @CREDITO, @TOTAL 
    
    	 -- Carrega a proxima linha da tabela
    	 FETCH NEXT FROM C_SOMA INTO @TIPO, @VALOR, @DEBITO, @CREDITO;	
    	END
    
    	CLOSE C_SOMA
    	DEALLOCATE C_SOMA
    	
    	RETURN
    END
    GO
    
    
    SELECT * FROM TESTE()
    

     

     

     


    What would Brian Boitano do ?
    ((2B || !2B) is Question) ? Se não da certo como voce esta fazendo... Tente fazer de um jeito totalmente diferente....
    Wednesday, August 04, 2010 3:49 PM

All replies

  • Olá Êmili,

    Você pode criar um campo do tipo computed para o valor acumulado, tente este Script:

     

     

    alter table nomeTabela
    
    add debito decimal(18, 2) null
    
    go
    
    
    
    alter table nomeTabela
    
    add credito decimal(18, 2) null
    
    go
    
    
    
    alter table nomeTabela
    
    add acumulado  AS (case when (PAGAR_REC = 'R') then VR_BRUTO_MV + credito else VR_BRUTO_MV - debito end)
    
    go
    
    
    
    

     

    Acredito que funcione,

     

    Abs,

     

    Rafael Zaccanini

    Se a resposta de ajudou marque como útil

    • Proposed as answer by Oda Rob Tuesday, August 03, 2010 12:51 AM
    Monday, August 02, 2010 4:39 PM
  • Êmili, vc não precisa alterar a tabela. Só muda tua query. Não entendi as colunas q tu precisa somar, mas tu pode fazer assim:

     

    select <coluna1> + <coluna2> as [SOMA]

    from <tabela>...

     

    No caso caso da coluna saldo acumulado, faça algo do tipo:

     

    select

       case

            where PAGAR_REC = 'R' then PAGAR_REC

            else PAGAR_REC * -1

        end as [saldo acumulado]

    from ...

     

    Espero ter ajudado. Abraço


    Gabriel Bauermann | Representante New Art Webdesing Criação e manutenção de Web Sites Fone (51) 8182 6819 | Email: gabriel.bauermann@gmail.com
    Monday, August 02, 2010 4:52 PM
  • Pessoal, tentei utilizar os dois exemplos acima e cheguei a esse script:

     

    SELECT  FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.PAGAR_REC, FLMV.NATUR_MOV, FLMV.VR_BRUTO_MV,
    (case when flmv.pagar_rec = 'P' then -(flmv.vr_bruto_mv) end) as débito,
    (case when flmv.pagar_rec = 'R' then flmv.vr_bruto_mv end) as crédito,
    (case when flmv.pagar_rec = 'R' then flmv.vr_bruto_mv else (flmv.vr_bruto_mv) * -1 end) as acumulado
    FROM  FLMV INNER JOIN
       FLNT ON FLMV.EMPRESA = FLNT.EMPRESA AND FLMV.FILIAL = FLNT.FILIAL AND FLMV.NATUR_MOV = FLNT.NATUR_MOV AND 
       FLMV.NATUR_CONTA = FLNT.NATUR_CONTA AND FLMV.NATUR_TRANSACAO = FLNT.NATUR_TRANSACAO
    where FLMV.DT_MOVTO between '2010-07-01' and '2010-07-07' and flmv.natur_mov = 'admb'
    ORDER BY FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.NATUR_MOV, FLMV.NATUR_TRANSACAO
    

    O problema é que eu não posso criar nem alterar tabelas nesse banco,  apenas consultar os dados das já existentes.

    Com esse script consegui resolver o problema de criar as colunas débito, crédito e acumulado, porém os valores da coluna acumulado ainda não estão corretos. Não estão totalizando os valores da débito - crédito, apenas repetindo a informação.

    DT_MOVTO                  NUMERO   PAGAR_REC   NATUR_MOV   VR_BRUTO_MV   DEBITO           CREDITO        ACUMULADO

    2010-07-01 00:00:00    1              R                  ADMB             59000.0000        NULL                59000.0000    59000.0000
    2010-07-01 00:00:00    3              P                  ADMB             1000.0000          -1000.0000       NULL              -1000.0000
    2010-07-01 00:00:00    4              R                  ADMB             8500.0000          NULL                8500.0000      8500.0000
    2010-07-01 00:00:00    4              P                  ADMB             3000.0000          -3000.0000       NULL              -3000.0000
    2010-07-07 00:00:00    3              P                  ADMB             120.0000            -120.0000         NULL              -120.0000
    2010-07-07 00:00:00    4              P                  ADMB             265.0300            -265.0300         NULL              -265.0300
    2010-07-07 00:00:00    5              P                  ADMB             96.0000              -96.0000          NULL              -96.0000
    2010-07-07 00:00:00    6              P                  ADMB             48.0000              -48.0000          NULL              -48.0000
    2010-07-07 00:00:00    7              P                  ADMB             69.2500              -69.2500          NULL              -69.2500
    2010-07-07 00:00:00    8              P                 ADMB              1000.0000           -1000.0000      NULL              -1000.0000

     

    Existe alguma maneira de incluir tudo no mesmo case, ou utilizar uma função?

    Obrigada.

    Êmili.

    Monday, August 02, 2010 5:40 PM
  • Êmili, não entendi tua dúvida. Não entendi quais campos tu qr somar (ou subtrair), uma vez q tu não tm valores para débito e crédito. A não ser q tenha estes campos na tua tabela e tu não mencionou eles. Neste caso faça o seguinte. Onde tem:

    (case
     when
     flmv.pagar_rec = 'R'
     
    then flmv.vr_bruto_mv else (flmv.vr_bruto_mv) * -1
    end
    ) as acumulado

    troca por:

    (colunaCredito - colunaDebito) as
     acumulado

     


    Gabriel Bauermann | Representante New Art Webdesing Criação e manutenção de Web Sites Fone (51) 8182 6819 | Email: gabriel.bauermann@gmail.com
    Tuesday, August 03, 2010 12:11 PM
  • Gabriel, bom dia!

    As colunas débito e crédito não existem. Os valores que deverão constar nelas são da coluna VR_BRUTO_MV, por isso utilizei o case para diferenciar pela informação da coluna PAGAR_REC para onde deve ir cada valor. O que eu não estou conseguindo fazer é colocar na coluna Acumulado (que também não existe) a conta entre os valores das colunas débito e crédito. Exemplo:

    Valores da VR_BRUTO_MV: 1000,00 / 500,00 / 300,00 / 3000,00 / 200,00

    Dados da PAGAR_REC: 1000,00 R (um crédito)

                                       500,00 P (um débito)

                                       300,00 P (um débito)

                                       3000,00 R (um crédito)

                                       200,00 P (um débito)

     

    Através desses dados das duas colunas (reais) preciso gerar outras três colunas com os seguintes valores:

     

    Débito                   Crédito                       Acumulado

                                1000                           1000

    500                                                         500

    300                                                         200

                                3000                           3200

    200                                                         3000

     

    O que eu consegui através do case foi colocar os valores nas colunas débito e crédito, porem na coluna acumulado eu não consigo fazer a soma entre essas duas colunas: Crédito - Débito (ou vice-versa, o que entrar primeiro).

    Espero que tenha conseguido explicar melhor agora.

     

    Obrigada.

     

    Êmili.

     

     

     

    Tuesday, August 03, 2010 3:02 PM
  • Êmili, bom dia,

    O que ocorre é o seguinte:
    Você criou colunas através de cálculos certo? Estas colunas não existem pelo nome que você imagina ser DÉBITO ou CRÉDITO!

    Não funcionará assim: SELECT DEBITO - CREDITO AS RESULTADO

    Você precisa pegar o bloco inteiro de código de DEBITO e subratir o bloco inteiro de código do CREDITO
    Exemplo:
    SELECT (case when flmv.pagar_rec = 'P' then -(flmv.vr_bruto_mv) end) - (case when flmv.pagar_rec = 'R' then flmv.vr_bruto_mv end) AS 'TESTE'

    Copia e cola este código e faça um teste!
    Veja se ajuda

    Em todo caso, entendi o que você está fazendo e já utilizei muito. De uma olhada na função ISNULL, ajudará muito no tipo de desenvolvimento que você está idealizando!

    Abraços!


    DBA Vini
    Tuesday, August 03, 2010 3:25 PM
  • Vc pode criar Procedures ou usar Tabelas temporarias ? se puder... da uma olhada nisto aqui:

     

    CREATE TABLE #RESULT (TIPO CHAR(1), VALOR DECIMAL, DEBITO DECIMAL, CREDITO DECIMAL, TOTAL DECIMAL)
    
    DECLARE @TIPO AS CHAR
    DECLARE @VALOR AS DECIMAL
    DECLARE @DEBITO AS DECIMAL
    DECLARE @CREDITO AS DECIMAL
    DECLARE @TOTAL AS DECIMAL
    
    DECLARE C_SOMA CURSOR FOR 
    	SELECT 
    		PAGAR_REC, VR_BRUTO_MV, 
    		CASE WHEN PAGAR_REC = 'P' THEN VR_BRUTO_MV * -1 ELSE 0 END AS DEBITO, 
    		CASE WHEN PAGAR_REC = 'R' THEN VR_BRUTO_MV ELSE 0 END AS CREDITO 
    	FROM 
    		FLMV
    	
    OPEN C_SOMA
    
    SELECT @TOTAL = 0
    FETCH NEXT FROM C_SOMA INTO @TIPO, @VALOR, @DEBITO, @CREDITO;
    WHILE @@FETCH_STATUS = 0 BEGIN
    	SELECT @TOTAL = @TOTAL + (@DEBITO + @CREDITO)
    	INSERT INTO #RESULT SELECT @TIPO, @VALOR, @DEBITO, @CREDITO, @TOTAL	
    
    	FETCH NEXT FROM C_SOMA INTO @TIPO, @VALOR, @DEBITO, @CREDITO;	
    END
    CLOSE C_SOMA
    DEALLOCATE C_SOMA
    
    SELECT * FROM #RESULT
    
    DROP TABLE #RESULT
    
    
    /**************************************\
    TIPO VALOR  DEBITO  CREDITO  TOTAL
    ----  -------  ----------  ---------   -----
    R   59000   0      59000    59000
    P   1000   -1000    0       58000
    R   8500    0      8500    66500
    P   3000   -3000    0       63500
    P   120    -120     0      63380
    P   265    -265     0      63115
    P   96     -96      0      63019
    \**************************************/

    Tuesday, August 03, 2010 5:15 PM
  • Vinicius, boa tarde!

    Sim, eu sei que não funcionará esse tipo de select (select debito - credito as resultado), por isso queria incluir tudo no mesmo case.

    Testei o código que você me passou mas a coluna TESTE retornou NULL para todos os valores...

     

    SELECT  FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.PAGAR_REC, FLMV.NATUR_MOV, FLMV.VR_BRUTO_MV,
    ((case when flmv.pagar_rec = 'P' then -(flmv.vr_bruto_mv) end) - (case when flmv.pagar_rec = 'R' then flmv.vr_bruto_mv end)) AS 'TESTE'
    FROM  FLMV INNER JOIN
       FLNT ON FLMV.EMPRESA = FLNT.EMPRESA AND FLMV.FILIAL = FLNT.FILIAL AND FLMV.NATUR_MOV = FLNT.NATUR_MOV AND 
       FLMV.NATUR_CONTA = FLNT.NATUR_CONTA AND FLMV.NATUR_TRANSACAO = FLNT.NATUR_TRANSACAO
    where FLMV.DT_MOVTO between '2010-07-01' and '2010-07-31' and FLMV.NATUR_MOV = 'admb'
    ORDER BY FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.NATUR_MOV, FLMV.NATUR_TRANSACAO
    
    
    Êmili.

     

     

    Tuesday, August 03, 2010 5:23 PM
  • Oi Êmili,

    ta retornando NULL por causa da soma/subtração com valor nulo em um dos campos. Mas a dica do Vinícius tá valendo, porém sugiro uma pequena alteração:

    SELECT (case when flmv.pagar_rec = 'P' then -(flmv.vr_bruto_mv) else 0 end) - (case when flmv.pagar_rec = 'R' then flmv.vr_bruto_mv else 0 end) AS 'TESTE'
     


    Gabriel Bauermann | Representante New Art Webdesing Criação e manutenção de Web Sites Fone (51) 8182 6819 | Email: gabriel.bauermann@gmail.com
    Tuesday, August 03, 2010 5:34 PM
  • O que de fato você precisa apresentar nos campos nulos??? Qual das colunas de qual tabela??? Ou qual resultado de que conta você quer apresentar nessa TESTE que veio NULL??

    DICA: Coloque o que você quer no lugar dos nulos utilizando um ELSE, já tentou?

    A estrutura CASE é uma condicional, "e assim como IF" leva uma cláusula ELSE
    CASE WHEN flmv.pagar_rec = 'P' THEN (bloco de contas) ELSE flmv.(Campo que deseja trazer)

    ps: Boa Gabriel!


    DBA Vini
    Tuesday, August 03, 2010 5:48 PM
  • Rui, boa tarde!

     

    A solução que você me passou é exatamente o que estou precisando. Só que ainda estou com algumas dúvidas. Como ainda não sei trabalhar com tabelas temporárias, não sei como chamar esses campos criados para dentro do meu select (debito, credito e total).

    SELECT FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.PAGAR_REC, FLMV.NATUR_MOV, FLMV.VR_BRUTO_MV,
    FROM FLMV INNER JOIN
       FLNT ON FLMV.EMPRESA = FLNT.EMPRESA AND FLMV.FILIAL = FLNT.FILIAL AND FLMV.NATUR_MOV = FLNT.NATUR_MOV AND 
       FLMV.NATUR_CONTA = FLNT.NATUR_CONTA AND FLMV.NATUR_TRANSACAO = FLNT.NATUR_TRANSACAO
    where FLMV.DT_MOVTO between '2010-07-01' and '2010-07-31' and FLMV.NATUR_MOV = 'admb'
    ORDER BY FLMV.DT_MOVTO, FLMV.NUMERO, FLMV.NATUR_MOV, FLMV.NATUR_TRANSACAO
    

    Outra dúvida: no código que você passou eu preciso que os valores do campo Valor sejam alimentados pelos valores da coluna VR_BRUTO_MV da tabela FLMV, como eu faço isso?

    E a última duvida....você criou uma tabela temporária certo? Li que a tabela só fica em "execução" durante a conexão com o banco de dados, ou seja, ao desconectar a mesma é deletada do banco. Se eu chamar alguns campos dessa tabela em uma view, toda vez que a view for acessada essa tabela será criada automaticamente?

    Obrigada pela ajuda.

    Êmili.

    Tuesday, August 03, 2010 5:59 PM
  • Tabelas temporárias também podem participar de JOINS.
    DBA Vini
    Wednesday, August 04, 2010 12:28 PM
  • Então... vamos por partes....

    Não tem como usar tabela temporaria em View... vc a usa so para fazer um processamento momentario... no caso a ideia seria melhor entao criar uma Function

    CREATE FUNCTION TESTE ()
    RETURNS 
    @RESULT TABLE (TIPO CHAR(1), VALOR DECIMAL, DEBITO DECIMAL, CREDITO DECIMAL, TOTAL DECIMAL)
    AS
    BEGIN
    		
    	-- Declaração de Variaveis
    	DECLARE @TIPO AS CHAR
    	DECLARE @VALOR AS DECIMAL
    	DECLARE @DEBITO AS DECIMAL
    	DECLARE @CREDITO AS DECIMAL
    	DECLARE @TOTAL AS DECIMAL
    
    	-- Declaração do Cursos usado para ler linha a linha (algo como um DataReader do C#)
    	DECLARE C_SOMA CURSOR FOR 
    		SELECT 
    			PAGAR_REC, VR_BRUTO_MV, 
    			CASE WHEN PAGAR_REC = 'P' THEN VR_BRUTO_MV * -1 ELSE 0 END AS DEBITO, 
    			CASE WHEN PAGAR_REC = 'R' THEN VR_BRUTO_MV ELSE 0 END AS CREDITO 
    		FROM 
    			FLMV
    		
    	OPEN C_SOMA
    
    	SELECT @TOTAL = 0
    
    	-- Aqui é como o comando Read() do DataReader, le uma linha da query declada no Cursor e carrega os valores nas variaveis que estao apos o "INTO"
    	FETCH NEXT FROM C_SOMA INTO @TIPO, @VALOR, @DEBITO, @CREDITO;
    
    	-- Aqui vai fazer um While enquanto tiver registros na Tabela do Cursor
    	WHILE @@FETCH_STATUS = 0 BEGIN
    
    	-- Atualiza a variavel @TOTAL com o Valor do Saldo Total Atual
    		SELECT @TOTAL = @TOTAL + (@DEBITO + @CREDITO)
    
    	-- Insere o registro de movimentação na tabela temporaria
    		INSERT INTO @RESULT SELECT @TIPO, @VALOR, @DEBITO, @CREDITO, @TOTAL 
    
    	 -- Carrega a proxima linha da tabela
    	 FETCH NEXT FROM C_SOMA INTO @TIPO, @VALOR, @DEBITO, @CREDITO;	
    	END
    
    	CLOSE C_SOMA
    	DEALLOCATE C_SOMA
    	
    	RETURN
    END
    GO
    
    
    SELECT * FROM TESTE()
    

     

     

     


    What would Brian Boitano do ?
    ((2B || !2B) is Question) ? Se não da certo como voce esta fazendo... Tente fazer de um jeito totalmente diferente....
    Wednesday, August 04, 2010 3:49 PM
  • Rui, bom dia!

    Desculpe a demora em responder.

    A criação da função vai me ajudar bastante. Muito obrigada pela ajuda.

    Êmili.

    Thursday, August 05, 2010 12:48 PM