adding values only if the values are positive or greater than zero

D

Daniel Haro

Hi guys. I need some special help, please ;)

I need to correct an expression (called TotalKilosDeduccion as mentioned
below in the SQL code). This expression needs to sum only the expressions
with a positive value in them.

Here is the SQL code for my query:

SELECT factores.Folio,
([factores.FactorHumedad]*[factores.PesoNetoProducto])/1000 AS
DeduccionHumedad,
([factores.FactorImpurezas]*[factores.PesoNetoProducto])/1000 AS
DeduccionImpurezas,
([factores.FactorHongos]*[factores.PesoNetoProducto])/1000 AS
DeduccionHongos,
([factores.FactorCalor]*[factores.PesoNetoProducto])/1000 AS DeduccionCalor,
([factores.FactorInsectos]*[factores.PesoNetoProducto])/1000 AS
DeduccionInsectos,
([factores.FactorAgentes]*[factores.PesoNetoProducto])/1000 AS
DeduccionAgentes,
([factores.FactorGerminados]*[factores.PesoNetoProducto])/1000 AS
DeduccionGerminados,
([factores.FactorPartidos]*[factores.PesoNetoProducto])/1000 AS
DeduccionPartidos,
([factores.FactorManchados]*[factores.PesoNetoProducto])/1000 AS
DeduccionManchados,
([factores.FactorAmpollados]*[factores.PesoNetoProducto])/1000 AS
DeduccionAmpollados,
([factores.FactorContrastantes]*[factores.PesoNetoProducto])/1000 AS
DeduccionContrastantes,
([factores.FactorAfines]*[factores.PesoNetoProducto])/1000 AS
DeduccionAfines,
[DeduccionHumedad]+[DeduccionImpurezas]+[DeduccionHongos]+[DeduccionCalor]+[DeduccionInsectos]+[DeduccionAgentes]+[DeduccionGerminados]+[DeduccionPartidos]+[DeduccionManchados]+[DeduccionAmpollados]+[DeduccionContrastantes]+[DeduccionAfines] AS TotalKilosDeduccion,
[pesonetoproducto]-[totalkilosdeduccion] AS PesoNetoAPagar, *
FROM Precio, factores;
 
S

Sylvain Lafontaine

If you are really on SQL-Server (as stated in the name of this newsgroup),
then simply use a Case statement:

Case When [DeduccionHumedad] > 0 Then [DeduccionHumedad] Else 0 End +
Case When [DeduccionHongos] > 0 Then [DeduccionHongos] Else 0 End +
... as TotalKilosDeduccion

If you are not on SQL-Server but are using JET/Access, then you must the
IIF() expression:

iif (([DeduccionHumedad] > 0), [DeduccionHumedad], 0) +
iif (([DeduccionHongos] > 0), [DeduccionHongos], 0) +
... as TotalKilosDeduccion
 
D

Daniel Haro

Thank You Sylvain

Your answer was exactly what I needed

Regards
Daniel

Sylvain Lafontaine said:
If you are really on SQL-Server (as stated in the name of this newsgroup),
then simply use a Case statement:

Case When [DeduccionHumedad] > 0 Then [DeduccionHumedad] Else 0 End +
Case When [DeduccionHongos] > 0 Then [DeduccionHongos] Else 0 End +
... as TotalKilosDeduccion

If you are not on SQL-Server but are using JET/Access, then you must the
IIF() expression:

iif (([DeduccionHumedad] > 0), [DeduccionHumedad], 0) +
iif (([DeduccionHongos] > 0), [DeduccionHongos], 0) +
... as TotalKilosDeduccion

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Daniel Haro said:
Hi guys. I need some special help, please ;)

I need to correct an expression (called TotalKilosDeduccion as mentioned
below in the SQL code). This expression needs to sum only the expressions
with a positive value in them.

Here is the SQL code for my query:

SELECT factores.Folio,
([factores.FactorHumedad]*[factores.PesoNetoProducto])/1000 AS
DeduccionHumedad,
([factores.FactorImpurezas]*[factores.PesoNetoProducto])/1000 AS
DeduccionImpurezas,
([factores.FactorHongos]*[factores.PesoNetoProducto])/1000 AS
DeduccionHongos,
([factores.FactorCalor]*[factores.PesoNetoProducto])/1000 AS
DeduccionCalor,
([factores.FactorInsectos]*[factores.PesoNetoProducto])/1000 AS
DeduccionInsectos,
([factores.FactorAgentes]*[factores.PesoNetoProducto])/1000 AS
DeduccionAgentes,
([factores.FactorGerminados]*[factores.PesoNetoProducto])/1000 AS
DeduccionGerminados,
([factores.FactorPartidos]*[factores.PesoNetoProducto])/1000 AS
DeduccionPartidos,
([factores.FactorManchados]*[factores.PesoNetoProducto])/1000 AS
DeduccionManchados,
([factores.FactorAmpollados]*[factores.PesoNetoProducto])/1000 AS
DeduccionAmpollados,
([factores.FactorContrastantes]*[factores.PesoNetoProducto])/1000 AS
DeduccionContrastantes,
([factores.FactorAfines]*[factores.PesoNetoProducto])/1000 AS
DeduccionAfines,
[DeduccionHumedad]+[DeduccionImpurezas]+[DeduccionHongos]+[DeduccionCalor]+[DeduccionInsectos]+[DeduccionAgentes]+[DeduccionGerminados]+[DeduccionPartidos]+[DeduccionManchados]+[DeduccionAmpollados]+[DeduccionContrastantes]+[DeduccionAfines]
AS TotalKilosDeduccion,
[pesonetoproducto]-[totalkilosdeduccion] AS PesoNetoAPagar, *
FROM Precio, factores;
Click to expand...
Click to expand...
 
Top