TA (7)

Listar mediante una función los empleados que han realizado ventas superiores al promedio durante el año 1997 junto a los clientes que han realizado compras inferiores al promedio en el mismo año, la función debe retornar los nombres, ciudad, región, código postal, país, ventas y compras en una sola tabla.

Modificar la función de tal modo que se aplique lo mismo pero para cualquier mes y año.

CODIGO

CREATE FUNCTION LISTAR_EMPLEADOS(@Mes INT,@Anno INT)

RETURNS @Resultado TABLE(Names NVARCHAR(40),City NVARCHAR(15),Region NVARCHAR(15),

PostalCode NVARCHAR(10),Country NVARCHAR(15),Ventas FLOAT,

Compras FLOAT)

AS

BEGIN

DECLARE @promedio FLOAT

SELECT @promedio=SUM((d.Quantity * d.unitprice)/ d.Quantity)

FROM [Order Details] d INNER JOIN Orders o ON o.orderid=d.orderid

WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno

INSERT INTO @Resultado

SELECT E.LastName +' '+ E.FirstName Nombre,E.City,E.Region,E.PostalCode,E.Country,

SUM(d.Quantity * d.unitprice) Ventas,0.00 Compras

FROM EMPLOYEES E JOIN ORDERS O ON E.EmployeeID=O.EmployeeID JOIN [Order Details] D ON O.orderid=D.orderid

WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno

GROUP BY E.LastName,E.FirstName ,E.City,E.Region,E.PostalCode,E.Country

HAVING SUM(d.Quantity * d.unitprice) >@promedio

UNION

SELECT C.CompanyName Nombre,C.City,C.Region,C.PostalCode,C.Country,

0.00 Ventas,sum(d.Quantity * d.unitprice) Compras

FROM CUSTOMERS C INNER JOIN ORDERS O ON C.CustomerID=O.CustomerID

INNER JOIN [Order Details] D ON O.orderid=D.orderid

WHERE MONTH(o.orderdate)=@Mes AND YEAR(o.orderdate)=@Anno

GROUP BY C.CompanyName,C.City,C.Region,C.PostalCode,C.Country

HAVING SUM(d.Quantity * d.unitprice) <@promedio

RETURN

END