Doubt with for no sql server 2008 command

Asked

Viewed 61 times

0

I have the following situation, 5 discount fields that receive the discount name 01 until 05. this sql query works below (1), makes the correct calculation only when it has only 1 of the filled out discounts, if I have more than 1 then the result will not be correct. In order for the result to be correct if you have more than 1 discount, I need to find the individual value of each discount on the value of the price is there after that I would have an overall discount on the value, I could add up all discounts, I would have to do one for is calculate each one separately.

How could I do that? I appreciate it!

DECLARE @VALOR_ENCONTRADO1 real
DECLARE @VALOR_ENCONTRADO2 real
DECLARE @VALOR_ENCONTRADO3 real
DECLARE @VALOR_ENCONTRADO4 real
DECLARE @VALOR_ENCONTRADO5 real

@VALOR_ENCONTRADO1 =(DESCONTO01  / 100) * PRECO  
@VALOR_ENCONTRADO2 =(DESCONTO02  / 100) * PRECO  
@VALOR_ENCONTRADO3 =(DESCONTO03  / 100) * PRECO  
@VALOR_ENCONTRADO4 =(DESCONTO04  / 100) * PRECO  
@VALOR_ENCONTRADO5 =(DESCONTO05  / 100) * PRECO  

(1)
select 
case (ADICIONAL_ESTADO)  
  when 'S' then   (SUM(DESCONTO01 + DESCONTO02 + DESCONTO03 + DESCONTO04 + 0 ) / 100) * PRECO    
  when 'N' then   (SUM(DESCONTO01 + DESCONTO02 + DESCONTO03 + DESCONTO04+DESCONTO05) / 100)  * PRECO
END AS  VALOR_LIQUIDO, 

case (ADICIONAL_ESTADO)  
  when 'S' then  (SUM(DESCONTO01 + DESCONTO02 + DESCONTO03 + DESCONTO04 + 0 ) / 100 ) * PROMOCAO 
  when 'N' then  (SUM(DESCONTO01 + DESCONTO02 + DESCONTO03 + DESCONTO04+DESCONTO05) / 100 ) * PROMOCAO
END AS VALOR_PROMOCAO, 
IDPRODUTO,CODIGO,PRECO,DESCONTO01,DESCONTO02,DESCONTO03,DESCONTO04,DESCONTO05
from TB_PRODUTO
WHERE CODIGO = '016055IR'
GROUP BY ADICIONAL_ESTADO, IDPRODUTO,CODIGO, PRECO,PROMOCAO,DESCONTO01,DESCONTO02,DESCONTO03,DESCONTO04,DESCONTO05

inserir a descrição da imagem aqui

Sql after posting the answer:

select 
(DESCONTO01 / 100 ) * PRECO AS DESCONTO_PRECO_01,
(DESCONTO02 / 100 ) * PRECO AS DESCONTO_PRECO_02,
(DESCONTO03 / 100 ) * PRECO AS DESCONTO_PRECO_03,
(DESCONTO04 / 100 ) * PRECO AS DESCONTO_PRECO_04,

case (ADICIONAL_ESTADO )  
  when 'S' then   (0 / 100 ) * PRECO   --0 é o valor do desconto para o estado caso tenha vai receber um valor
END AS DESCONTO_PRECO_05,  

(DESCONTO01 / 100 ) * PRECO AS DESCONTO_PRECO_01,
(DESCONTO02 / 100 ) * PRECO AS DESCONTO_PRECO_02,
(DESCONTO03 / 100 ) * PRECO AS DESCONTO_PRECO_03,
(DESCONTO04 / 100 ) * PRECO AS DESCONTO_PRECO_04,
case (ADICIONAL_ESTADO )  
  when 'N' then   (DESCONTO05 / 100 ) * PRECO    -- se não tem desconto para o estado vai receber o campo de desconto05 
END AS DESCONTO_PRECO_05,  

IDPRODUTO,CODIGO,PRECO,DESCONTO01,DESCONTO02,DESCONTO03,DESCONTO04,DESCONTO05
from TB_PRODUTO
WHERE CODIGO = '016055IR'
GROUP BY ADICIONAL_ESTADO, IDPRODUTO,CODIGO, PRECO,PROMOCAO,DESCONTO01,DESCONTO02,DESCONTO03,DESCONTO04,DESCONTO05

Image Answer:

inserir a descrição da imagem aqui

sql-server

1 answer

0


I found the question a little confusing (rs), and it would be nice to see the structure of the tables, but I hope I can help. To find the individual value of each discount, you could do so:

select 
(DESCONTO01 / 100 ) * PRECO AS DESCONTO_PRECO_01,
(DESCONTO02 / 100 ) * PRECO AS DESCONTO_PRECO_02,
(DESCONTO03 / 100 ) * PRECO AS DESCONTO_PRECO_03,
(DESCONTO04 / 100 ) * PRECO AS DESCONTO_PRECO_04,
case (ADICIONAL_ESTADO)     
  when 'N' then   (DESCONTO05 / 100)  * PRECO ,
END AS DESCONTO_PRECO_05, 

(DESCONTO01 / 100 ) * PROMOCAO AS DESCONTO_PROMOCAO_01,
(DESCONTO02 / 100 ) * PROMOCAO AS DESCONTO_PROMOCAO_02,
(DESCONTO03 / 100 ) * PROMOCAO AS DESCONTO_PROMOCAO_03,
(DESCONTO04 / 100 ) * PROMOCAO AS DESCONTO_PROMOCAO_04,
case (ADICIONAL_ESTADO)  
   when 'N' then  (DESCONTO05 / 100 ) * PROMOCAO
END AS DESCONTO_PROMOCAO_05, 

IDPRODUTO, CODIGO, PRECO, DESCONTO01, DESCONTO02, DESCONTO03, DESCONTO04, DESCONTO05
from TB_PRODUTO
  WHERE CODIGO = '016055IR'
GROUP BY ADICIONAL_ESTADO, IDPRODUTO, CODIGO, PRECO, PROMOCAO, DESCONTO01, DESCONTO02, DESCONTO03, DESCONTO04, DESCONTO05

In this query is only displaying the discount values, but from there it is only do the desired operations to find your "global discount".

  • Your help was fundamental, I appreciate, but I adjusted the question with the image and sql of the final adjustment, I wonder if it is possible to add the value of the discount in the same field, since it came in separate fields

  • Yes. Just replace the comma that separates the fields by the plus sign (+). This way, will add up all the discounts. For example: select (DESCONTO01 / 100 ) * DESCONTO_PRECO_01 + (DESCONTO02 / 100 ) * AS PRICES DESCONTO_PRECO_02 + (DESCONTO03 / 100 ) * AS PRICES DESCONTO_PRECO_03 + ...

  • it didn’t work because DESCONTO_PRECO_01 doesn’t exist, but thank you anyway.

  • use isnull. Ex: Isnull(DESCONTO_PRECO_01, 0) Will return 0 if the field value is null.

  • sorry my lack but I could not use so that was correct. If you manage to change your answer I thank you.

Browser other questions tagged sql-server

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