Multiply by the largest in sql

Question

Multiply by the largest in sql

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I have the following consultation

SELECT   
   f.SE_Factura, 
   SE_Consecutivo_Embarque,
   f.SE_Orden_De_Compra,
   f.SE_Orden_De_Venta,
   f.SE_Fecha_OV,   
   f.SE_Cliente_Despachar_A,
   (f.SE_Peso*e.SE_Cantidad_Embarcada)*400 as Peso, 
   (f.SE_Volumen*e.SE_Cantidad_Embarcada)*400 as Volumen    
FROM SE_FactEmbarque f  
INNER JOIN SE_Embarques e  
ON  e.SE_Orden_De_Venta = f.SE_Orden_De_Venta   
AND (e.SE_Codigo_Articulo = f.SE_EAN_Articulo OR e.SE_Codigo_Articulo = 
f.SE_Codigo_Articulo) 
where f.SE_Factura = 'CD-40348'

which brings me the following data

Now what I am trying to do and must do is to know which of the two is greater if the weight or the volume and multiply it by the rate of the following table

Where SE_Cliente_Despachar_A is equal to SE_Cliente

sql sql-server

asked by Eduard 31.05.2017 в 14:49

source

2 answers

1

Using CASE WHEN you can solve your problem, that is, first validate if the Peso is greater than Volumen and perform the operation:

SELECT   
   f.SE_Factura, 
   SE_Consecutivo_Embarque,
   f.SE_Orden_De_Compra,
   f.SE_Orden_De_Venta,
   f.SE_Fecha_OV,   
   f.SE_Cliente_Despachar_A,
   (f.SE_Peso*e.SE_Cantidad_Embarcada)*400 as Peso, 
   (f.SE_Volumen*e.SE_Cantidad_Embarcada)*400 as Volumen,
   CASE 
        WHEN Peso > Volumen THEN (Peso * SE_Tarifa)
        ELSE (Volumen * SE_Tarifa)
    END 
FROM SE_FactEmbarque f  
INNER JOIN SE_Embarques e  
ON  e.SE_Orden_De_Venta = f.SE_Orden_De_Venta   
AND (e.SE_Codigo_Articulo = f.SE_EAN_Articulo OR e.SE_Codigo_Articulo = 
f.SE_Codigo_Articulo) 
where f.SE_Factura = 'CD-40348'

answered by 31.05.2017 в 15:01

Separate elements from a list identifying a set of letters Slow VirtualBox with Linux [closed]

score 3 · Accepted Answer

The use of the statement CASE is very useful in these cases, I add the new column to your query, I hope it works for you.

SELECT   
   f.SE_Factura, 
   SE_Consecutivo_Embarque,
   f.SE_Orden_De_Compra,
   f.SE_Orden_De_Venta,
   f.SE_Fecha_OV,   
   f.SE_Cliente_Despachar_A,
   (f.SE_Peso*e.SE_Cantidad_Embarcada)*400 as Peso, 
   (f.SE_Volumen*e.SE_Cantidad_Embarcada)*400 as Volumen,
   (CASE WHEN f.SE_Peso > f.SE_Volumen THEN f.SE_Peso ELSE f.SE_Volumen) * e.SE_Cantidad_Embarcada
FROM SE_FactEmbarque f  
INNER JOIN SE_Embarques e  
    ON  e.SE_Orden_De_Venta = f.SE_Orden_De_Venta   
    AND (e.SE_Codigo_Articulo = f.SE_EAN_Articulo OR e.SE_Codigo_Articulo = 
    f.SE_Codigo_Articulo) 
where f.SE_Factura = 'CD-40348'