none
Procedimiento almacenado (usando DB Northwind) RRS feed

  • Pregunta

  • Usando la base de datos Northwind, necesito realizar un procedimiento almacenado que reciba como parámetro el año. Calcular la cantidad de órdenes y recaudaciones hechas por un empleado para un determinado año. Aplicar una comisión del 25 % en caso que la cantidad de órdenes supere el promedio de ese año. Promedio=(Cantidad de órdenes / Número de Empleados) de lo contrario solo aplicar el 10%

    Hasta ahora, esto es lo que llevo:

    CREATE PROCEDURE SP_Empleado
    	@Año int
    AS
    DECLARE @Promedio int
    DECLARE @Cantidad int
    DECLARE @Cant int
    DECLARE @Comision money
    SET @Cant = (Select COUNT(*) FROM Orders WHERE YEAR(OrderDate) = @Año) 
    SET @Cantidad = (SELECT COUNT(*) FROM Employees)
    SET @Promedio= (SELECT AVG(od.Quantity / @Cantidad) from [Order Details] od)
    IF(@Promedio > @Cant)
    	BEGIN
    		SET @Comision = (SELECT (SUM(od.Quantity * od.UnitPrice)*0.25) FROM [Order Details] od
    							inner join Orders O on od.OrderID = o.OrderID
    							WHERE YEAR(o.OrderDate) = @Año
    							)
    	END
    ELSE
    	BEGIN
    		SET @Comision = (SELECT (SUM(od.Quantity * od.UnitPrice)*0.10) FROM [Order Details] od
    							inner join Orders O on od.OrderID = o.OrderID
    							WHERE YEAR(o.OrderDate) = @Año
    							)
    	END
    SELECT
    	o.EmployeeID,
    	e.FirstName,
    	SUM(od.Quantity * od.UnitPrice) AS Recaudación
    FROM [Order Details] od
    INNER JOIN Orders o ON od.OrderID = o.OrderID
    INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID
    WHERE YEAR(o.OrderDate) = @Año

    Y no sé si estoy yendo por el lado correcto. Espero que puedan ayudarme, se los agradecería mucho.

    sábado, 6 de abril de 2019 2:31

Respuestas

  • Ya resolví y al final, tenía un pequeño error a la hora de aplicar el SET a @Comisión. Al final quedaría así, tal vez no sea lo más práctico, pero es funcional:

    DECLARE @Promedio INT DECLARE @CantOrdenes INT DECLARE @CantEmpleado INT DECLARE @Comisión MONEY SET @CantOrdenes = (SELECT COUNT(o.OrderID) FROM Orders o WHERE YEAR(o.OrderDate) = 1997) SET @CantEmpleado = (SELECT COUNT(*) FROM Employees) SET @Promedio = (@CantOrdenes/@CantEmpleado) IF(@CantOrdenes > @Promedio) BEGIN SET @Comisión = 0.25 END ELSE BEGIN SET @Comisión = 0.10 END SELECT o.EmployeeID AS IdEmpleado, e.FirstName AS [Primer Nombre], COUNT(o.OrderID) AS [Cantidad de Órdenes], SUM(od.UnitPrice) AS [Recaudación], SUM(od.UnitPrice * @Comisión) AS Comisión FROM Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID WHERE YEAR(o.OrderDate) = 1997 GROUP BY o.EmployeeID, e.FirstName

    Al hacerlo manualmente, el resultado es el mismo cuando ejecuto la consulta.


    • Editado Jordy4VZ sábado, 6 de abril de 2019 17:58 Error
    • Marcado como respuesta Jordy4VZ domingo, 7 de abril de 2019 13:14
    sábado, 6 de abril de 2019 17:57

Todas las respuestas

  • Hola Jordy4VZ:

    Yo no lo enfocaría como tú. Y como entiendo que lo que quieres es aprender, y no que te lo de realizado, yo lo haría del siguiente modo.

    Lo primero, es que si accedes a los datos para obtener media, suma y numero, realmente puedes hacerlo en una sentencia con un group by, aunque todavía no puedas aplicarle el cálculo de la comisión.

    El resultado de esta query, entonces le aplicaría lo que hay que calcular. Envolviendo la misma en una cte o tabla derivada.

    Este código te lo entrego sin pulir, tu tendrás que verificarlo y completarlo.

    Si necesitas más ayuda, solo tienes que pedirla.

    DECLARE @year INT= 1997;
    
    DECLARE @periodoDesde DATETIME;
    
    DECLARE @periodoHasta DATETIME;
    
    SET @periodoDesde = CAST(CAST(@year AS VARCHAR(4)) + '0101' AS DATETIME);
    
    SET @periodoHasta = DATEADD(year, 1, @periodoDesde);
    
    WITH cte
    	AS (SELECT COUNT(o.OrderID) AS num
    		    , SUM(od.Quantity) AS cantidad
    		    , SUM(od.UnitPrice) AS precio
    		    , AVG(od.quantity) AS media
    		    , e.FirstName
    	    FROM   
    		    orders o
    			    INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID
    			    LEFT JOIN [Order Details] od ON o.OrderID = od.OrderID
    	    WHERE  o.OrderDate >= @periodoDesde
    			 AND o.OrderDate < @periodoHasta
    	    GROUP BY e.FirstName)
    	SELECT cte.num
    		, cte.cantidad
    		, cte.precio
    		, cte.media
    		, cte.FirstName
    	FROM   
    		cte;

    sábado, 6 de abril de 2019 6:57
  • Al final lo simplifiqué y quedó así:

    SELECT
    	o.EmployeeID AS IdEmpleado,
    	e.FirstName AS [Primer Nombre],
    	COUNT(o.OrderID) AS [Cantidad de Órdenes],
    	SUM(od.UnitPrice) AS [Recaudación],
    	ROUND(SUM(od.UnitPrice * 25/100), 2) AS Comisión
    FROM Orders o
    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID
    WHERE YEAR(o.OrderDate) = 1997
    GROUP BY o.EmployeeID, e.FirstName

    Olvidé mencionar que, lo que se tiene que mostrar en pantalla es lo siguiente:

    IdEmpleado   -   Primer Nombre  -    Cantidad de órdenes  -  Recaudación - Comisión
                4             Alfred                                 18                    10254        2563.5

    Si ejecuto la consulta, me muestra exactamente lo del ejemplo, pero en la comisión, sí o sí tengo que multiplicarla por 0.25 ó 0.10. Hice lo siguiente, otra vez, con mi primer ejemplo:

    DECLARE @Promedio INT	
    DECLARE @CantOrdenes INT
    DECLARE @CantEmpleado INT
    DECLARE @Comisión MONEY
    SET @CantOrdenes = (SELECT COUNT(o.OrderID) FROM Orders o WHERE YEAR(o.OrderDate) = 1997)
    SET @CantEmpleado = (SELECT COUNT(*) FROM Employees)
    SET @Promedio = (@CantOrdenes/@CantEmpleado)
    IF(@CantOrdenes > @Promedio)
    	BEGIN
    		SET @Comisión = (SELECT SUM(od.UnitPrice * 25/100) 
    							FROM Orders o 
    							INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    							WHERE YEAR(o.OrderDate) = 1997
    							)	
    	END
    ELSE
    	BEGIN
    		SET @Comisión = (SELECT SUM(od.UnitPrice * 10/100) 
    							FROM Orders o 
    							INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    							WHERE YEAR(o.OrderDate) = 1997
    							)	
    	END
    SELECT
    	o.EmployeeID AS IdEmpleado,
    	e.FirstName AS [Primer Nombre],
    	COUNT(o.OrderID) AS [Cantidad de Órdenes],
    	SUM(od.UnitPrice) AS [Recaudación],
    	@Comisión AS Comisión
    FROM Orders o
    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID
    WHERE YEAR(o.OrderDate) = 1997
    GROUP BY o.EmployeeID, e.FirstName

    Y todo bien, menos la comisión, que me muestra '6903.77' para todos los empleados.

    sábado, 6 de abril de 2019 17:47
  • Ya resolví y al final, tenía un pequeño error a la hora de aplicar el SET a @Comisión. Al final quedaría así, tal vez no sea lo más práctico, pero es funcional:

    DECLARE @Promedio INT DECLARE @CantOrdenes INT DECLARE @CantEmpleado INT DECLARE @Comisión MONEY SET @CantOrdenes = (SELECT COUNT(o.OrderID) FROM Orders o WHERE YEAR(o.OrderDate) = 1997) SET @CantEmpleado = (SELECT COUNT(*) FROM Employees) SET @Promedio = (@CantOrdenes/@CantEmpleado) IF(@CantOrdenes > @Promedio) BEGIN SET @Comisión = 0.25 END ELSE BEGIN SET @Comisión = 0.10 END SELECT o.EmployeeID AS IdEmpleado, e.FirstName AS [Primer Nombre], COUNT(o.OrderID) AS [Cantidad de Órdenes], SUM(od.UnitPrice) AS [Recaudación], SUM(od.UnitPrice * @Comisión) AS Comisión FROM Orders o INNER JOIN [Order Details] od ON o.OrderID = od.OrderID INNER JOIN Employees e ON o.EmployeeID = e.EmployeeID WHERE YEAR(o.OrderDate) = 1997 GROUP BY o.EmployeeID, e.FirstName

    Al hacerlo manualmente, el resultado es el mismo cuando ejecuto la consulta.


    • Editado Jordy4VZ sábado, 6 de abril de 2019 17:58 Error
    • Marcado como respuesta Jordy4VZ domingo, 7 de abril de 2019 13:14
    sábado, 6 de abril de 2019 17:57