Uso de la sentencia MAX() en UDF

[CarlosGPT]

Base de datos NorthWind

Buenas tardes estimados, el ejercicio que deseo realizar me pide lo
siguiente:

Utilizando la función MAX() crear una función que muestre el
registro del empleado que haya sacado más dinero por sus órdenes de venta.

Para hacer este ejercicio voy a utilizar otra función que he
realizado previamente llamada "costoTotal", esta función me da como
salida el costo total de una orden de venta con solo recibir el ID de la orden:

ALTER FUNCTION [dbo].[costoTotal](@ordenID int)

RETURNS table

RETURN (SELECT [Order
Details].OrderID,

ROUND(SUM((UnitPrice
-(UnitPrice*Discount)) * Quantity), 2)

AS Total_Orden FROM [Order Details]

WHERE
OrderID = @ordenID

GROUP
BY OrderID)



si la ejecuto:

select * from dbo.costoTotal(10250)



me da la siguiente salida:

OrderID Total_Orden

10250 1552.60



Ahora, utilizando esta función he creado la función que se me pide:



ALTER FUNCTION dbo.Maxima_Ventas()

RETURNS @table_resultado table (IdEmpleado int, Primer_Nombre
nvarchar(10), Monto_total money)

AS

BEGIN

DECLARE @max_monto money;

SELECT @max_monto=MAX(res.Monto_total)

FROM(

SELECT SUM(c.Total_Orden) Monto_total

FROM Orders o

CROSS APPLY dbo.costoTotal(o.OrderID) c

GROUP BY o.EmployeeID

) res

INSERT INTO @table_resultado

SELECT o.EmployeeID, e.FirstName, @max_monto

FROM Orders o

JOIN Employees e ON o.EmployeeID = e.EmployeeID

GROUP BY o.EmployeeID, e.FirstName

RETURN

END



Pero al ejecutarla:

SELECT * FROM dbo.Maxima_Ventas



Obtengo el resultado correcto en la columna "Monto_total"

Pero me salen los registros de todos los empleados y sus ID:



idEmpleado
Primer_Nombre Monto_Total

1 Andrew 232890.84

2 Janeth 232890.84

3 Margaret 232890.84

4 Steven 232890.84

5 Michael 232890.84

6 etc... etc...

7

8

9



Pero la respuesta que yo busco es la siguiente, correspondiente al
empleado Margareth:

idEmpleado
Primer_Nombre Monto_Total

3 Margaret 232890.84



De antemano les agradezco su ayuda.

[Neftali [Germán.Estévez]]

Bienvenido a los foros.
Te recomiendo que revises la Guía de estilo y que utilices TAG's en los mensajes cuando añadas código.
Es difícil leer la pregunta tal y como está.

[engranaje]

Si no me equivoco, dbo.Maxima_Ventas() devuelve @table_resultado que es rellenada aquí:

Código SQL [-]
INSERT INTO @table_resultado
SELECT o.EmployeeID, e.FirstName, @max_monto
FROM Orders o
JOIN Employees e ON o.EmployeeID = e.EmployeeID
GROUP BY o.EmployeeID, e.FirstName

Esta sentencia inserta en @table_resultado todos los registros de Orders para los que existe un registro en Employees con el mismo valor en el campo EmployeeID.

Todo el trabajo que haces previamente en dbo.Maxima_Ventas() lo realizas para obtener @max_monto pero en el insert fuerzas ese valor siempre en el tercer campo de @table_resultado y entiendo que lo que buscas es que solo te muestre el empleado cuyo SUM(c.Total_Orden) Monto_total tiene el valor máximo.

Comenta si estoy en lo cierto.

[engranaje]

Igual en el mensaje anterior la pista no fue muy clara. Para confirmar lo que comento puedes probar a modificar el insert de esta forma:

Código SQL [-]

INSERT INTO @table_resultado

SELECT o.EmployeeID, e.FirstName, SUM(c.Total_Orden) Monto_total 

FROM Orders o

CROSS APPLY dbo.costoTotal(o.OrderID) c

JOIN Employees e ON o.EmployeeID = e.EmployeeID

GROUP BY o.EmployeeID, e.FirstName

having SUM(c.Total_Orden)= @max_monto

[CarlosGPT]

Es correcto amigo lo que busco es que solo me muestre el empleado cuyo SUM(c.Total_Orden) Monto_total tiene el valor máximo.
Voy a tomar en cuenta tu consejo y probaré el código modificando el insert.
Muchas gracias por la ayuda.

[CarlosGPT]

Al modificar el segmento del codigo en la funcion dbo.Maxima_Ventas de la siguiente forma; no me sale ningún dato en la salida, la tabla sale vacía:

Código SQL [-]
ALTER FUNCTION dbo.Maxima_Ventas()

RETURNS @table_resultado table (IdEmpleado int, Primer_Nombre
nvarchar(10), Monto_total money)

AS

BEGIN

DECLARE @max_monto money;

SELECT @max_monto=MAX(res.Monto_total)

FROM(

SELECT SUM(c.Total_Orden) Monto_total

FROM Orders o

CROSS APPLY dbo.costoTotal(o.OrderID) c

GROUP BY o.EmployeeID

) res

INSERT INTO @table_resultado

SELECT o.EmployeeID, e.FirstName, SUM(c.Total_Orden) Monto_total 

FROM Orders o

JOIN Employees e ON o.EmployeeID = e.EmployeeID

CROSS APPLY dbo.costoTotal(o.OrderID) c 

GROUP BY o.EmployeeID, e.FirstName

having SUM(c.Total_Orden)= @max_monto

RETURN

END

-- SELECT * FROM dbo.Maxima_Ventas()

[abelg]

Que tal, espero esto te ayude.

Código SQL [-]
CREATE FUNCTION dbo.Maximo_Ventas()
RETURNS @table_resultado TABLE (IdEmpleado int, Primer_Nombre nvarchar(10), Monto_total money)
AS
BEGIN
  DECLARE @TABLE TABLE (RowNumber int, IdEmpleado int, Primer_Nombre nvarchar(10), Monto_total money) 
  INSERT @TABLE
  SELECT ROW_NUMBER() OVER(ORDER BY ROUND(SUM((UnitPrice -(UnitPrice*Discount)) * Quantity), 2) desc) AS RowNumber, e.EmployeeID, e.FirstName, ROUND(SUM((UnitPrice -(UnitPrice*Discount)) * Quantity), 2) AS Total_Orden
  FROM Employees e
  INNER JOIN Orders o on o.EmployeeID = e.EmployeeID
  INNER JOIN [Order Details] od on od.OrderID = o.OrderID
  GROUP BY e.EmployeeID,
    e.FirstName

  INSERT INTO @table_resultado
  SELECT TOP 1 IdEmpleado, Primer_Nombre, Monto_total
  FROM @TABLE
  RETURN
END
GO

SELECT * FROM dbo.Maximo_Ventas()

Resultado
4 Margaret 232890.85

Saludos