--Crear e insertar las columnas de aquellos empledos que vendieron productos en el
mes de octubre del a�o 1996
use Northwind
go
CREATE VIEW V_EMPLEADOSPORPERIODO
AS
select DISTINCT [Link] AS IDEMPLEADO, [Link] + ' ' +[Link] AS
EMPLEADO
--INTO EMPLEADOSPORPERIODO
from Employees E --ARCHIVO TEMPORAL
INNER JOIN Orders O ON [Link] = [Link]
INNER JOIN [Order Details] OD
ON [Link] = [Link]
WHERE MONTH([Link])=10
AND YEAR([Link])=1996
--ORDER BY [Link] UNA CLAUSULA VIEW, NO ACEPTA ORDER VIEW
GO
--LEER LOS DATOS
SELECT * FROM V_EMPLEADOSPORPERIODO
GO
-- VIEW, SE CREA UNA TABLA QUE SI ESTA ENGANCHADA POR LO QUE CUALQUIER CAMBIO EN LA
TABLA ORIGINAL
--TAMBIEN SE REFLEJA EN LA TABLA VIEW, PERO INTO SOLO ES TEMPORAL
-- MOSTRAR LAS ORDENES EFECTUADAS Y EL IMPORTE VENDIDO
SELECT [Link] AS IDORDEN, SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN[Order Details] OD
ON [Link]= [Link]
GROUP BY [Link]
ORDER BY [Link]
GO
-------------------------------------------------------
--MOSTRAR LAS ORDENES EFECTUADAS Y LOS PRODUCTOS VENDIDOS
SELECT [Link] AS IDORDEN, [Link] AS PRODUCTO,
SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
GROUP BY [Link], [Link]
ORDER BY [Link], [Link]
GO
-----------------------------------------------------------------------------------
-----------------------
--MOSTRAR LAS ORDENES EFECTUADAS Y LOS PRODUCTOS VENDIDOS PERO AQUELLOS CON IMPORTE
MENOR A 100
SELECT [Link] AS IDORDEN, [Link] AS PRODUCTO,
SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
GROUP BY [Link], [Link]
HAVING SUM([Link]*[Link])<100 --- CUANDIO HAVING ES EL WHERE DE LOS
GROUP
ORDER BY [Link], [Link]
GO
-----------------------------------------------------------------------------------
-----------------------
--AL FINAL DE CADA GRUPO AGREGAR EL TOTAL DEL IMPORTE
--CLUSULA ROLLUP
SELECT [Link] AS IDORDEN, [Link] AS PRODUCTO,
SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
GROUP BY [Link], [Link]
WITH ROLLUP
ORDER BY [Link], [Link]
GO
-----------------------------------------------------------------------------------
-----------------------
--AL FINAL DE CADA GRUPO AGREGAR EL TOTAL DEL IMPORTE
--CLUSULA CUBE
SELECT [Link] AS IDORDEN, [Link] AS PRODUCTO,
SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
GROUP BY [Link], [Link]
WITH CUBE
ORDER BY [Link], [Link]
GO
-----------------------------------------------------------------------------------
-----------------------
--AL FINAL DE CADA GRUPO AGREGAR EL TOTAL DEL IMPORTE
--
DECLARE @MES INT, @ANIO INT
SET @MES = 09
SET @ANIO = 1996
SELECT [Link] AS IDORDEN, [Link] AS PRODUCTO,
SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
WHERE MONTH([Link])=@MES
AND YEAR([Link])=@ANIO
GROUP BY [Link], [Link]
ORDER BY [Link], [Link]
GO
-----------------------------------------------------------------------------------
-----------------------
--AL FINAL DE CADA GRUPO AGREGAR EL TOTAL DEL IMPORTE
--
ALTER PROCEDURE SP_EMPLEADOSPORPERIODO
(@MES INT, @ANIO INT, @MES2 INT
)
AS
BEGIN
SELECT [Link] AS IDORDEN, [Link] AS PRODUCTO,
SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
WHERE MONTH([Link]) BETWEEN @MES AND @MES2
AND YEAR([Link])=@ANIO
GROUP BY [Link], [Link]
ORDER BY [Link], [Link]
END
GO
EXECUTE SP_EMPLEADOSPORPERIODO 8,1996,12
GO
-----------------------------------------------------------------------------------
-----------------------
--MOSTRAR LAS ORDENES Y SUS DETALLES DE PRODUCTO M�S EL IMPORTE PERO SOLAMENTE DE
AQUELLAS VENTAS DE LOS MESES DE ENERO A MARZO DEL A�O 1996
IF OBJECT_ID('SP_VENTASXPERIODOS') IS NOT NULL
DROP PROCEDURE SP_VENTASXPERIODOS
GO
CREATE PROCEDURE SP_VENTASXPERIODOS
@MES1 VARCHAR(20), @MES2 VARCHAR(20), @A�O INT
AS BEGIN
SELECT [Link] AS IDORDEN, [Link] AS PRODUCTO,
SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
WHERE DATENAME(MM, [Link]) BETWEEN @MES1 AND @MES2
ORDER BY [Link], [Link]
END
GO
EXECUTE SP_VENTASXPERIODOS 'ENERO', 'MARZO', 1996
go
IF OBJECT_ID('SP_VENTASXmeses') IS NOT NULL
DROP PROCEDURE SP_VENTASXmeses
GO
CREATE PROCEDURE SP_VENTASXmeses
@A�O INT
AS BEGIN
SELECT [Link] AS IDORDEN,DATENAME(mm, [Link]) as Fecha, [Link] AS
PRODUCTO, SUM([Link]*[Link]) AS IMPORTE FROM Orders O
JOIN [Order Details] OD
ON [Link] = [Link]
JOIN Products P
ON [Link] = [Link]
WHERE DATENAME(MM, [Link]) not LIKE '%r%'
GROUP BY [Link], [Link],[Link]
ORDER BY [Link], [Link], [Link]
END
GO
EXECUTE SP_VENTASXmeses 1996