SHOW MAX, MIN of several columns sql 2008

Question

SHOW MAX, MIN of several columns sql 2008

Navigation

#1 by (3 votes)

1

select nombre,Company,isnull([4],0) as 'ABRIL',isnull([2],0) as 'FEBRERO',isnull([9],0) as 'SETIEMBRE',
'TOTAL' = (isnull([4],0)+isnull([2],0)+isnull([9],0)),PROMEDIO = ((isnull([4],0)+isnull([2],0)+isnull([9],0)))/3,
'MAXIMO' = (CASE WHEN isnull([4],0) > isnull([2],0) AND isnull([4],0) > isnull([9],0) THEN isnull([4],0) ELSE 
CASE WHEN isnull([2],0) > isnull([4],0) AND isnull([2],0) > isnull([4],0) THEN isnull([2],0) ELSE 
CASE WHEN isnull([9],0) > isnull([4],0) AND isnull([9],0) > isnull([2],0) THEN isnull([9],0) ELSE 0 
END END END)
from
(select e.firstname 'nombre',c.CompanyName 'Company',od.UnitPrice 'CANTIDAD'
,DATEPART(MONTH,O.OrderDate) as DATA_MES 
from Employees e,Customers c,orders o,[Order Details] od
where
o.CustomerID = c.CustomerID and 
o.EmployeeID = e.EmployeeID and
od.OrderID = o.OrderID
group by e.firstname,c.CompanyName,od.UnitPrice,O.OrderDate
) AS MESTABLE
PIVOT(
SUM(CANTIDAD) 

FOR DATA_MES IN ([4], [2], [9])
)AS PIV_MES

I need to find the minimum of these 3 months I wanted to do it in the same way that I find the maximum? Does anyone know another perhaps more simplified way of doing it?

sql-server

asked by dasio 13.02.2018 в 16:16

source

1 answer

pass variables in Laravel JSON.parse gives error but with eval correct

score 3 · Answer 1

Uff, there are several things to fix in your query.

First of all, I always recommend using the scheme of the table as a prefix (in this case, I'm assuming it's dbo). On the other hand, you should use explicit joins instead of these implicit joins (which are deprecated more than 10 years ago).

Finally, I do not see the reason for making a pivot, if later the truth is that it is not used. The code that I would use is the following:

WITH CTE AS
(
    SELECT  e.firstname Nombre,
            c.CompanyName Company,
            DATEPART(MONTH,O.OrderDate) Mes,
            SUM(od.UnitPrice) Cantidad
    FROM dbo.Employees e
    INNER JOIN dbo.orders o
        ON o.EmployeeID = e.EmployeeID
    INNER JOIN dbo.Customers c
        ON o.CustomerID = c.CustomerID
    INNER JOIN dbo.[Order Details] od
        ON od.OrderID = o.OrderID
    GROUP BY e.firstname,
             c.CompanyName,
             od.UnitPrice
)
SELECT  Nombre,
        Company,
        MAX(CASE WHEN Mes = 2 THEN Cantidad END) Febrero,
        MAX(CASE WHEN Mes = 4 THEN Cantidad END) Abril,
        MAX(CASE WHEN Mes = 9 THEN Cantidad END) Septiembre,
        MAX(Cantidad) Maximo,
        MIN(Cantidad) Minimo
FROM CTE
WHERE Mes IN (2,4,9)
GROUP BY Nombre,
         Company
;