none
Actualizar tabla Customers [DB Northwind] RRS feed

  • Pregunta

  • Usando la base de datos Northwind, necesito agregar la columna "Categoría" de tipo varchar(50) a la tabla Customers.
    Establecer un procedimiento almacenado para actualizar la categoría de todos los clientes (Customers) en base a las siguientes reglas:
           -Oro: Son todos aquellos clientes que han realizado más de 6 órdenes por año, tomando en cuenta solamente los últimos tres años.
           -Plata: Son todos aquellos clientes que han realizado más de 10 órdenes en el último año.
           -Bronce: Son todos aquellos clientes que tienen menos de 10 órdenes en el último año.
    Al ejecutar el procedimiento se deberá actualizar todas las categorías de los clientes.

    Con la siguiente consulta, puedo ver la cantidad de órdenes realizadas por cada cliente:

    --Creo tabla Categoría:
    ALTER TABLE Customers ADD Categoría varchar(20)
    --Consulta
    SELECT 
    	o.CustomerID, 
    	COUNT(*) AS Cantidad
    FROM Orders o
    GROUP BY o.CustomerID

    Mi problema es que, no encuentro la forma de tomar las cantidades de órdenes realizadas por cada cliente, para luego compararlas usando IF y así actualizar la categoría de cada cliente.

    En teoría, el procedimiento almacenado quedaría así:

    CREATE PROCEDURE SP_Categoría
    AS
    DECLARE @CantOrden INT
    SET @CantOrden = (SELECT COUNT(o.OrderID) FROM Orders o)
    IF(@CantOrden > 12)
    	BEGIN
    		UPDATE Customers SET Categoría = 'Oro'
    	END
    ELSE IF(@CantOrden >= 10 AND @CantOrden < 12)
    	BEGIN
    		UPDATE Customers SET Categoría = 'Plata'
    	END
    ELSE IF(@CantOrden < 10)
    	BEGIN
    		UPDATE Customers SET Categoría = 'Bronce'
    	END


    • Editado Jordy4VZ domingo, 7 de abril de 2019 4:00
    sábado, 6 de abril de 2019 22:20

Respuestas

  • Hola Jordy4VZ:

    No es necesario utilizar un if para esto.

    DECLARE @fecha DATETIME=
    (
        SELECT MAX(orderDAte)
        FROM   
    	    orders
    );
    
    DECLARE @fechaMin DATETIME= DATEADD(year, -3, @fecha);
    
    DECLARE @FECHAPL DATETIME= DATEADD(YEAR, -1, @FECHA);
    
    WITH ORO
    	AS (SELECT COUNT(*) AS num
    		    , customerid
    		    , 1 AS CATEGORIA
    	    FROM   
    		    Orders
    	    WHERE  orderDate > @fechaMin
    	    GROUP BY CustomerID
    	    HAVING COUNT(*) > 6),
    	PLATAYBRONCE
    	AS (SELECT COUNT(*) AS num
    		    , customerid
    		    , 2 AS CATEGORIA
    	    FROM     
    		    Orders
    	    WHERE   orderDate > @FECHAPL
    	    GROUP BY CustomerID
    	    EXCEPT
    	    SELECT NUM
    		    , CustomerID
    		    , CATEGORIA
    	    FROM   
    		    ORO
        ), CATEGORIAS AS (
    	SELECT o.num
    		, o.CustomerID
    		, O.CATEGORIA
    		
    	FROM     
    		ORO O
    	UNION ALL
    	SELECT PyB.num
    		, PyB.CustomerID
    		, PyB.CATEGORIA
    		
    	FROM   
    		PLATAYBRONCE PyB
    	)
    	SELECT c.CustomerID, case when c.categoria = 1 then 'ORO'
    						  WHEN c.CATEGORIA = 2 and c.num >= 10 then 'PLATA'
    						  WHEN C.CATEGORIA = 2 AND C.NUM < 10 THEN 'BRONCE' END AS CATEGORIA FROM CATEGORIAS c
    	ORDER BY CATEGORIA

    En el primer conjunto recojo los oros, en el segundo las platas y bronces exceptuando los oros ya recogidos. En el conjunto categorias y solo por hacerlo un poco más organizado y de un modo más didáctico, juntamos unos y otros. Con la salida se muestran como tienen que ser.

    De este modo tenemos los clientes categorizados, sin pensar en ir fila por fila.

    Por tanto la solución en un procedure puede ser así:

    CREATE PROCEDURE SP_CATEGORIA 
    AS
    
    DECLARE @fecha DATETIME=
    (
        SELECT MAX(orderDAte)
        FROM   
    	    orders
    );
    
    DECLARE @fechaMin DATETIME= DATEADD(year, -3, @fecha);
    
    DECLARE @FECHAPL DATETIME= DATEADD(YEAR, -1, @FECHA);
    
    WITH ORO
    	AS (SELECT COUNT(*) AS num
    		    , customerid
    		    , 1 AS CATEGORIA
    	    FROM   
    		    Orders
    	    WHERE  orderDate > @fechaMin
    	    GROUP BY CustomerID
    	    HAVING COUNT(*) > 6),
    	PLATAYBRONCE
    	AS (SELECT COUNT(*) AS num
    		    , customerid
    		    , 2 AS CATEGORIA
    	    FROM     
    		    Orders
    	    WHERE   orderDate > @FECHAPL
    	    GROUP BY CustomerID
    	    EXCEPT
    	    SELECT NUM
    		    , CustomerID
    		    , CATEGORIA
    	    FROM   
    		    ORO
        ), CATEGORIAS AS (
    	SELECT o.num
    		, o.CustomerID
    		, O.CATEGORIA
    		
    	FROM     
    		ORO O
    	UNION ALL
    	SELECT PyB.num
    		, PyB.CustomerID
    		, PyB.CATEGORIA
    		
    	FROM   
    		PLATAYBRONCE PyB
    	)UPDATE CUSTOMERS SET CATEGORIA = 
    	 case when c.categoria = 1 then 'ORO'
    						  WHEN c.CATEGORIA = 2 and c.num >= 10 then 'PLATA'
    						  WHEN C.CATEGORIA = 2 AND C.NUM < 10 THEN 'BRONCE' END 
    						  FROM CUSTOMERS INNER JOIN CATEGORIAS c ON CUSTOMERS.CustomerID = C.CustomerID
    	RETURN


    • Marcado como respuesta Jordy4VZ domingo, 7 de abril de 2019 19:59
    domingo, 7 de abril de 2019 16:03