-- obtener todas las ordenes
select * from orders

select * from [Order Details]

-- Unicidad de Orders

insert into orders (orderid) values (10248)
-- Cannot insert explicit value for identity column in table 'Orders' when IDENTITY_INSERT is set to OFF.

create table personas (
idPersona int not null primary key,
nomPersona varchar(50))

insert into personas (idPersona, nomPersona) values (1,'Victor')
(1 row affected)
insert into personas (idPersona, nomPersona) values (1,'Luis')
-- Violation of PRIMARY KEY constraint 'PK__personas__A4788141B872AD97'. Cannot insert duplicate 
-- key in object 'dbo.personas'. The duplicate key value is (1).


-- intentear duplicar la llave del OrderDetails
insert into [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
values (10248, 11, 14, 20, 0)

-- Violation of PRIMARY KEY constraint 'PK_Order_Details'. Cannot insert duplicate key in 
-- object 'dbo.Order Details'. The duplicate key value is (10248, 11).


-- obtener todas las ordenes
select * from orders where orderid = 10248

select * from [Order Details] where orderid = 10248

select * from Employees where EmployeeID = 5

select * from Orders where EmployeeID = 5

-- Conjuntos JOIN
select * from orders where orderid = 10248

select * from [Order Details] where orderid = 10248

select OrderId, CustomerId, EmployeeId, OrderDate, ProductId, UnitPrice, Quantity
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
-- Ambiguous column name 'OrderId'. => Decidir de que tabla debo obtener la columna

-- JOIN de dos tablas (DEBE EXISTIR en ambas tablas)
select o.OrderId, CustomerId, EmployeeId, OrderDate, ProductId, UnitPrice, Quantity
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID

-- JOIN + WHERE
select o.OrderId, CustomerId, EmployeeId, OrderDate, ProductId, UnitPrice, Quantity
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
where o.OrderID = 10248

-- las ventas en detalle del empleado = 1
select o.OrderId, CustomerId, EmployeeId, OrderDate, ProductId, UnitPrice, Quantity
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
where o.EmployeeID = 1


-- las ventas que superen las 60 unidades
select o.OrderId, CustomerId, EmployeeId, OrderDate, ProductId, UnitPrice, Quantity
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
where od.Quantity > 60

-- las ventas que superen las 60 unidades pero solo de oct 2019
select o.OrderId, CustomerId, EmployeeId, OrderDate, ProductId, UnitPrice, Quantity
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
where od.Quantity > 60 and
o.OrderDate between '2019-10-01' and '2019-10-31'

-- las ventas suma (unitprice * quantity) solo del 2019 por cliente
-- p1 solo las ventas del 2019
select o.OrderId, CustomerId, EmployeeId, OrderDate, ProductId, UnitPrice, Quantity
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
where o.OrderDate between '2019-01-01' and '2019-12-31'

select o.CustomerId, sum(UnitPrice * Quantity) SubTotal
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
where o.OrderDate between '2019-01-01' and '2019-12-31'
group by CustomerId

-- las ventas del 2019
select sum(UnitPrice * Quantity) SubTotal
from orders o
JOIN [Order Details] od ON od.OrderID = o.OrderID
where o.OrderDate between '2019-01-01' and '2019-12-31'

-- ventas por categoria
select CategoryID, CategoryName, sum(UnitPrice * Quantity) SubTotal
from [Order Details] od
join Products p ON p.ProductID = od.ProductID
join Categories c ON c.CategoryID = p.CategoryID
group by CategoryID, CategoryName

Msg 209, Level 16, State 1, Line 104
Ambiguous column name 'CategoryID'.
Msg 209, Level 16, State 1, Line 100
Ambiguous column name 'CategoryID'.
Msg 209, Level 16, State 1, Line 100
Ambiguous column name 'UnitPrice'.

select c.CategoryID, CategoryName, sum(p.UnitPrice * Quantity) SubTotal
from [Order Details] od
join Products p ON p.ProductID = od.ProductID
join Categories c ON c.CategoryID = p.CategoryID
group by c.CategoryID, CategoryName

-- ventas por categoria del 2019

select c.CategoryID, CategoryName, sum(p.UnitPrice * Quantity) SubTotal
from [Order Details] od
join Products p ON p.ProductID = od.ProductID
join Categories c ON c.CategoryID = p.CategoryID
join Orders o ON o.OrderID = od.OrderID
where o.OrderDate between '2019-01-01' and '2019-12-31'
group by c.CategoryID, CategoryName

-- Cuadratura
select * 
from hr.dbo.employees e-- 107
join hr.dbo.departments d on d.department_id = e.department_id

-- debo encontrar la fila que no hace match
-- LEFT JOIN 
select * 
from hr.dbo.employees e-- 107
left join hr.dbo.departments d on d.department_id = e.department_id

-- encontrar la fila
-- NULL solo se puede evaluar con IS / NO PUEDO USAR = NULL
select * 
from hr.dbo.employees e-- 107
left join hr.dbo.departments d on d.department_id = e.department_id
where d.department_id = null
-- No filas
select * 
from hr.dbo.employees e-- 107
left join hr.dbo.departments d on d.department_id = e.department_id
where d.department_id is null

-- No filas
select * 
from hr.dbo.employees e-- 107
left join hr.dbo.departments d on d.department_id = e.department_id
where e.department_id is null

select * from hr.dbo.employees where employee_id = 178

select * from hr.dbo.departments

-- RIGHT == tomando un left -- JOIN (106) + TODAS las filas de la tabla RIGHT que no hacen match con la tabla employees
select * 
from hr.dbo.employees e-- 122
RIGHT join hr.dbo.departments d on d.department_id = e.department_id
-- validando que no existe empleados para el departamento 240
select * from hr.dbo.employees where department_id = 240

-- RIGHT == 107 = left join
select * 
from hr.dbo.departments d -- 122
RIGHT join hr.dbo.employees e on d.department_id = e.department_id

-- Clientes que no registran Orders 
SELECT customers.CustomerID, customers.CompanyName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
where Orders.OrderID is null

select * from Orders where CustomerID = 'FISSA'

SELECT Customers.CompanyName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
where orders.OrderID is null

-- FULL
-- 1 LEFT JOIN + 2 right join
select * 
from hr.dbo.employees e
full join hr.dbo.departments d on d.department_id = e.department_id


-- Sub Consulta
select * from Orders where orderid = 10248

select * from [Order Details]
-- encontrar el promedio de las cantidades
select avg(quantity) from [Order Details]

-- obtener el detalle de ventas cuando la cantidad del pedido supere el promedio de la cantidad
select * from [Order Details] where Quantity > (select avg(quantity) from [Order Details])

-- obtener el detalle de ventas cuando la cantidad del pedido supere el promedio de la cantidad
-- Solo del 2019

select od.* 
from [Order Details] od
left join Orders o ON o.OrderID = od.OrderID
where Quantity > (select avg(quantity) from [Order Details])
and o.OrderDate between '2019-01-01' and '2019-12-31'

-- cuanto es el importe de ventas que superan el promedio de la cantidad
select sum(unitprice * quantity) 
from [Order Details] where Quantity > (select avg(quantity) from [Order Details])

-- cuanto es el importe de ventas que superan el promedio de la cantidad 
-- solo 2019
select sum(unitprice * quantity) 
from [Order Details] od
left join orders o on o.orderid = od.orderid
where Quantity > (select avg(quantity) from [Order Details])
and o.OrderDate between '2019-01-01' and '2019-12-31'

-- ventas por categoria
select c.CategoryID, CategoryName, sum(od.UnitPrice * Quantity) SubTotal
from [Order Details] od
join Products p ON p.ProductID = od.ProductID
join Categories c ON c.CategoryID = p.CategoryID
group by c.CategoryID, CategoryName

-- total 
select sum(quantity * unitprice) Total
from [Order Details] --1354458.59

select c.CategoryID, CategoryName, sum(od.UnitPrice * Quantity) SubTotal,
	(select sum(quantity * unitprice) Total from [Order Details]) TotalVta,
	sum(od.UnitPrice * Quantity) / (select sum(quantity * unitprice) Total from [Order Details]) * 100 [% Part. Venta]
from [Order Details] od
join Products p ON p.ProductID = od.ProductID
join Categories c ON c.CategoryID = p.CategoryID
group by c.CategoryID, CategoryName

-- sacando los ventas por cliente y el importe por cada año

-- Rango de operaciones

select min(orderdate), max(orderdate) from Orders
-- 2019-07-04 00:00:00.000	2021-05-06 00:00:00.000

select customerid, count(1) NroPedidos, sum(unitprice * quantity) SubTotal
from orders o
left join [Order Details] od ON od.OrderID = o.OrderID
group by CustomerID
-- Cuadratura
select count(1) from orders where customerid= 'BLONP' -- 11
26	19088.00

select * 
from orders o
left join [Order Details] od ON od.OrderID = o.OrderID
where CustomerID = 'BLONP' -- 26


select customerid, count(distinct o.OrderId) NroPedidos, sum(unitprice * quantity) SubTotal
from orders o
left join [Order Details] od ON od.OrderID = o.OrderID
where o.OrderDate between '2019-01-01' and '2019-12-31'
group by CustomerID

select customerid, count(distinct o.OrderId) NroPedidos, sum(unitprice * quantity) SubTotal
from orders o
left join [Order Details] od ON od.OrderID = o.OrderID
where o.OrderDate between '2020-01-01' and '2020-12-31'
group by CustomerID

select customerid, count(distinct o.OrderId) NroPedidos, sum(unitprice * quantity) SubTotal
from orders o
left join [Order Details] od ON od.OrderID = o.OrderID
where o.OrderDate between '2021-01-01' and '2021-12-31'
group by CustomerID

-- todo junto
select c.customerid, companyname, y19.NroPedidos [#Ped 2019], y19.SubTotal [ST 2019],
	y20.NroPedidos [#Ped 2020], y20.SubTotal [ST 2020],
	y21.NroPedidos [#Ped 2021], y21.SubTotal [ST 2021],
	case when y19.SubTotal < y20.SubTotal and y20.SubTotal < y21.SubTotal then 1 else 0 end flgEvaluacion
from Customers c
left join (select customerid, count(distinct o.OrderId) NroPedidos, sum(unitprice * quantity) SubTotal
			from orders o
			left join [Order Details] od ON od.OrderID = o.OrderID
			where o.OrderDate between '2019-01-01' and '2019-12-31'
			group by CustomerID) y19 on y19.CustomerID = c.CustomerID
left join (select customerid, count(distinct o.OrderId) NroPedidos, sum(unitprice * quantity) SubTotal
			from orders o
			left join [Order Details] od ON od.OrderID = o.OrderID
			where o.OrderDate between '2020-01-01' and '2020-12-31'
			group by CustomerID) y20 on y20.CustomerID = c.CustomerID
left join (select customerid, count(distinct o.OrderId) NroPedidos, sum(unitprice * quantity) SubTotal
			from orders o
			left join [Order Details] od ON od.OrderID = o.OrderID
			where o.OrderDate between '2021-01-01' and '2021-12-31'
			group by CustomerID) y21 on y21.CustomerID = c.CustomerID