Practica PC2

/*5.	Seleccionar por país el cliente a quien más se le ha vendido
y el número de órdenes que se emitieron para ese cliente, solo de las
ventas del 2017 y el país de envío sea el mismo del cliente,
ordenado por país y nombre cliente. Se debe mostrar país,
id cliente, nombre cliente, numero ordenes, importe vendido*/

create view vw_pc2_ej5 as
select c.Country, c.CustomerID, c.CompanyName, Count(o.OrderID)Cantidad,
SUM(od.Quantity*od.UnitPrice*(1-od.Discount))Monto
from Customers c join Orders o on c.CustomerID=o.CustomerID join [Order Details] od on o.OrderID=od.OrderID
where year(o.OrderDate)=2017 and o.ShipCountry=c.Country
group by c.Country, c.CustomerID, c.CompanyName

select *
from vw_pc2_ej5 vw
where vw.Monto>=(select max(svw.Monto) from vw_pc2_ej5 svw where vw.Country=svw.Country)
order by vw.Monto desc

 /* 6. Liste los productos que se quedarían desabastecidos
 si la venta en unidades se multiplica por 2.
 Muestre el código del producto,
 su stock actual, la venta en unidades actual y
 la que sería si se multiplica por 2.*/

 select p.ProductID, p.UnitsInStock, SUM(od.Quantity)VentaActual, SUM(od.Quantity)*2'VentaNueva'
 from Products p join [Order Details] od on p.ProductID=od.ProductID join Orders o on o.OrderID=od.OrderID
 group by p.ProductID, p.UnitsInStock
 having SUM(od.Quantity)*2>p.UnitsInStock

/* 7 Mostrar el proveedor que
 tuvo la menor venta (MONTO) de productos en un año 2017*/

 create view vw_pc2_ej7 as
 select s.SupplierID, s.CompanyName, year(o.OrderDate)'Año', SUM(od.Quantity*od.UnitPrice*(1-od.Discount))'Monto'
 from Suppliers s join Products p on p.SupplierID=s.SupplierID join [Order Details] od on od.ProductID=p.ProductID
				  join Orders o on o.OrderID=od.OrderID
 where year(o.OrderDate)=2017
 group by s.SupplierID, s.CompanyName, year(o.OrderDate)


 select *
 from vw_pc2_ej7 vw
 where vw.Monto<=(select min(svw.Monto) from vw_pc2_ej7 svw)

/* 8 Muestre el territorio y nombre de los jefes cuyos empleados han superado dos órdenes vendidas y más de
1000 en monto vendido. Estos jefes serán acreedores de un premio siempre y cuando
 la diferencia en días entre la fecha de la orden y la fecha de despacho no sea mayor a 7.*/

 select t.TerritoryDescription, j.EmployeeID, j.FirstName, SUM(od.Quantity*od.UnitPrice*(1-od.Discount))'Monto'
 from Territories t join EmployeeTerritories et on et.TerritoryID=t.TerritoryID
					join Employees j on j.EmployeeID=et.EmployeeID
					join Employees e on j.EmployeeID=e.ReportsTo
					join Orders o on o.EmployeeID=e.EmployeeID
					join [Order Details] od on o.OrderID=od.OrderID
where DATEDIFF(dd,o.OrderDate,o.ShippedDate)<7 and o.ShippedDate is not null
group by t.TerritoryDescription, j.EmployeeID, j.FirstName
having Count(o.OrderID)>2 and SUM(od.Quantity*od.UnitPrice*(1-od.Discount))>1000
order by t.TerritoryDescription asc

 /*9 Listar los empleados y el producto que más vendió en cantidad de Órdenes
 realizado en un determinado año 2017.
Tome como base la fecha de la orden (orderdate)*/
/*Empleado, el producto, la cantidad*/

create view vw_pc2_ej9 as
select e.EmployeeID, e.FirstName+' '+e.LastName 'Nombre', p.ProductID, COUNT(od.OrderID)'Cantidad', p.ProductName
from Employees e join Orders o on e.EmployeeID=o.EmployeeID join [Order Details] od on o.OrderID=od.OrderID
				 join Products p on p.ProductID=od.ProductID
where year(o.OrderDate)=2017
group by e.EmployeeID, e.FirstName+' '+e.LastName, p.ProductID, p.ProductName


select *
from vw_pc2_ej9 vw where vw.Cantidad= (select max(svw.Cantidad) from vw_pc2_ej9 svw where vw.EmployeeID=svw.EmployeeID)
order by vw.EmployeeID asc