Make a Max a subquery

Question

Make a Max a subquery

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a problem, since I have a table which has the fields of VAT, price, productname, date, what I want is to obtain the maximum, as of a date, and I do it, but now I need that After obtaining the maximum price, check if the VAT is 0 or 1, if it is 1 multiply it by 1.16 and if it is 0 you will not realize anything. This is the code I'm trying to use.

select MAX(CASE WHEN iva='1' then Precio*1.16 end)as Precio from Gastos 
 WHERE NombreConcepto = 'REFRESCO'and FechaGasto>='20170101'

This code gets me the maximum but it has VAT, and if there is another higher price that has no VAT, it excludes it, first I want to take the maximum to the Price column, then see if I have to multiply it by 1.16 and if not so leave it ... some solution.

sql-server

asked by Javier Hernandez 30.05.2018 в 20:33

source

2 answers

0

You only need to include the price without VAT in the clause ELSE

select MAX(CASE WHEN iva='1' then Precio*1.16 else Precio end)as Precio from Gastos WHERE NombreConcepto = 'REFRESCO'and FechaGasto>='20170101'

or if it is MAXIMUM and then apply VAT

select top 1 CASE WHEN iva='1' then Precio*1.16 else Precio end as Precio from Gastos WHERE NombreConcepto = 'REFRESCO'and FechaGasto>='20170101' order by Precio desc

answered by 30.05.2018 в 20:51

Error React-Native Access components of a scene from another scene

score 0 · Accepted Answer

We could do the following:

;WITH CTE AS (
  SELECT Precio,
     CASE WHEN iva='1' THEN Precio*1.16 ELSE Precio END PrecioMasIva
     FROM Gastos G
     WHERE  G.NombreConcepto = 'REFRESCO'
        AND G.FechaGasto>='20170101'

)
SELECT  MAX(T1.PrecioMasIva)
    FROM CTE T1
    INNER JOIN (SELECT MAX(Precio) MaxPrecioSinIva
            FROM CTE
        ) T2
        ON T1.Precio = T2.MaxPrecioSinIva

Detail:

We use a CTE to build an expense query with a% co_of gross% and a% co_of%
Leveraging the CTE , we recover those cases where the Precio equals the Precio mas iva
Finally we make a Precio of Precio Máximo sin Iva ( MAX() gross if you do not have VAT) to finally get the desired value.

Keep in mind that the result privileges cases that have VAT, for example if you had two expenses of $ 100, one with VAT and one without it, the maximum value of Precio más Iva would be 100 which would coincide with the two expenses , but finally the maximum value shown would be $ 116