TA - Northwind

----PREGUNTA 7------

CREATE FUNCTION Lista_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 @prom AS FLOAT
SELECT @prom = 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 AS Nombre,E.City,E.Region,E.PostalCode,E.Country,
            SUM(d.Quantity * d.unitprice) as Ventas,0.00 as Compras
    FROM
EMPLOYEES E INNER JOIN ORDERS O ON E.EmployeeID=O.EmployeeID INNER 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)>@prom
UNION
SELECT C.CompanyName as Nombre,C.City,C.Region,C.PostalCode,C.Country,
        0.00 as Ventas,sum(d.Quantity * d.unitprice) as 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)<@prom
    RETURN
   
    END
    GO

---PREGUNTA 8 -----

CREATE PROCEDURE uspProductoIUD (
    @ProductID int, @ProductName nvarchar(40), @SupplierID int,
    @CategoryID int, @QuantityPerUnit nvarchar(20),
    @UnitPrice money, @UnitsInStock smallint, @UnitsOnOrder smallint,
    @ReorderLevel smallint, @Discontinued bit,@Operacion int
)
AS BEGIN
    IF @Operacion = 1 BEGIN
        INSERT INTO Products
        (ProductName,SupplierID,CategoryID,QuantityPerUnit,UnitPrice,
         UnitsInStock,UnitsOnOrder,ReorderLevel,Discontinued)
        VALUES
        (@ProductName,@SupplierID,@CategoryID,@QuantityPerUnit,
         @UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued)
    END

    IF @Operacion = 2 BEGIN
        UPDATE Products
        SET      
            ProductName = @ProductName,SupplierID = @SupplierID,
            CategoryID = @CategoryID,QuantityPerUnit = @QuantityPerUnit,
            UnitPrice = @UnitPrice,UnitsInStock = @UnitsInStock,
            UnitsOnOrder = @UnitsOnOrder,ReorderLevel = @ReorderLevel,
            Discontinued = @Discontinued
        WHERE
            ProductID = @ProductID
    END

    IF @Operacion = 3 BEGIN
        DELETE FROM Products WHERE ProductID = @ProductID
    END
END