-- Zadanie1
SELECT DISTINCT YEAR(OrderDate)
FROM Sales.SalesOrderHeader
ORDER BY 1;

SELECT *
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) = (
	SELECT MIN(YEAR(OrderDate))
	FROM Sales.SalesOrderHeader
)

SELECT *
FROM Sales.SalesOrderHeader
WHERE MONTH(OrderDate) = 5


--Zadanie 2
SELECT Sales.Customer.CustomerID, MIN(LastName + ', ' + FirstName) "Imię, nazwisko",
	COUNT(*) "Liczba zamówień"
FROM Sales.Customer JOIN Person.Person ON Sales.Customer.CustomerID = Person.Person.BusinessEntityID
	JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID
GROUP BY Customer.CustomerID
HAVING COUNT(*) >25
ORDER BY 3 DESC;


DECLARE @ordersIn2011 TABLE(customerID INT, orderYear INT, numberOfOrders INT)
INSERT INTO @ordersIn2011
SELECT CustomerID, YEAR(OrderDate) orderYear, COUNT(*)
FROM Sales.SalesOrderHeader
WHERE (YEAR(OrderDate) != 2012
	OR YEAR(OrderDate) != 2014)
	AND YEAR(OrderDate) = 2011
GROUP BY CustomerID, YEAR(OrderDate)

DECLARE @ordersIn2014 TABLE(customerID INT, orderYear INT, numberOfOrders INT)
INSERT INTO @ordersIn2014
SELECT CustomerID, YEAR(OrderDate) orderYear, COUNT(*)
FROM Sales.SalesOrderHeader
WHERE (YEAR(OrderDate) != 2012
	OR YEAR(OrderDate) != 2014)
	AND YEAR(OrderDate) = 2014
GROUP BY CustomerID, YEAR(OrderDate)

SELECT customerID "klientID", orderYear "Rok", numberOfOrders "Liczba zamówień"
FROM @ordersIn2011
WHERE customerID IN (SELECT customerID FROM @ordersIn2014)
UNION ALL
SELECT customerID "klientID", orderYear "Rok", numberOfOrders "Liczba zamówień"
FROM @ordersIn2014
WHERE customerID IN (SELECT customerID FROM @ordersIn2011)
ORDER BY customerID


--Zadanie 3
CREATE TABLE Sprzedaz
(
	pracID INTEGER NOT NULL,
	prodID INTEGER NOT NULL,
	"Nazwa produktu" dbo.Name NOT NULL,
	Rok INTEGER NOT NULL,
	Ilość INTEGER NOT NULL,
);

INSERT INTO Sprzedaz
	SELECT SalesPersonID, Product.ProductID, MAX(Name), YEAR(OrderDate) orderYear, SUM(OrderQty)
	FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader
		ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
		JOIN Production.Product ON Product.ProductID = SalesOrderDetail.ProductID
		JOIN Sales.SpecialOfferProduct ON SpecialOfferProduct.ProductID = SalesOrderDetail.ProductID
	WHERE SalesPersonID IS NOT NULL
	GROUP BY SalesPersonID, Product.ProductID, YEAR(OrderDate);

SELECT * FROM dbo.Sprzedaz;
TRUNCATE TABLE dbo.Sprzedaz;


--z pivotem
SELECT piv.SalesPersonID, piv.ProductID, piv.Name, [2011], [2012], [2013], [2014]
FROM
(
	SELECT SalesPersonID, Product.ProductID, Name, YEAR(OrderDate) orderYear, OrderQty quantity
	FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader
		ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
		JOIN Production.Product ON Product.ProductID = SalesOrderDetail.ProductID
		JOIN Sales.SpecialOfferProduct ON SpecialOfferProduct.ProductID = SalesOrderDetail.ProductID
	WHERE SalesPersonID IS NOT NULL
) query
PIVOT
(
	SUM(quantity)
	FOR orderYear IN ([2011], [2012], [2013], [2014])
) piv
ORDER BY piv.SalesPersonID


--zadanie 4
--1)
SELECT YEAR(OrderDate) "Year", MONTH(OrderDate) "Month", COUNT(DISTINCT CustomerID) "Number of customers"
FROM Sales.SalesOrderHeader
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, MONTH

SELECT piv.saleYear, [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]
FROM
(
	SELECT DISTINCT YEAR(OrderDate) saleYear,
		MONTH(OrderDate) saleMonth,
		CustomerID
	FROM Sales.SalesOrderHeader
) query
PIVOT
(
	COUNT(CustomerID)
	FOR saleMonth IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) piv
ORDER BY piv.saleYear

--2)
SELECT YEAR(OrderDate) "Rok", MONTH(OrderDate) "Miesiac", DAY(OrderDate) "Dzien", SUM(TotalDue) "Suma kwot", COUNT(DISTINCT ProductID) "Liczba roznych towarów"
FROM Sales.SalesOrderDetail JOIN Sales.SalesOrderHeader
	ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
GROUP BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)
ORDER BY YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate);


--zadanie 6
DECLARE @platinum TABLE(CustomerID INTEGER, numberOfOrders Integer, transactionsAmount MONEY, color NVARCHAR(10))
INSERT INTO @platinum
SELECT goldQuery.CustomerID, SUM(goldQuery.numberOfOrders) numberOfOrders, SUM(goldQuery.transactionsAmount) transactionsAmount, 'Platynowa' color
FROM
	(
		SELECT CustomerID, COUNT(*) numberOfOrders, SUM(SubTotal) transactionsAmount, 'Złota' color
		FROM Sales.SalesOrderHeader
		WHERE CustomerID NOT IN (SELECT customerID FROM @platinum) AND
			  SubTotal > (SELECT AVG(SubTotal) * 1.5 FROM Sales.SalesOrderHeader)
		GROUP BY CustomerID
		HAVING COUNT(*) >= 2
	) goldQuery
	GROUP BY goldQuery.CustomerID
	HAVING COUNT(goldQuery.CustomerID) = (
		SELECT COUNT(DISTINCT YEAR(OrderDate))
		FROM Sales.SalesOrderHeader)

DECLARE @gold TABLE(CustomerID INTEGER, numberOfOrders Integer, transactionsAmount MONEY, color NVARCHAR(10))
INSERT INTO @gold
SELECT CustomerID, COUNT(*) numberOfOrders, SUM(SubTotal) transactionsAmount, 'Złota' color
FROM Sales.SalesOrderHeader
WHERE CustomerID NOT IN (SELECT customerID FROM @platinum) AND
	  SubTotal > (SELECT AVG(SubTotal) * 1.5 FROM Sales.SalesOrderHeader)
GROUP BY CustomerID
HAVING COUNT(*) >= 2


DECLARE @silver TABLE(CustomerID INTEGER, numberOfOrders Integer, transactionsAmount MONEY, color NVARCHAR(10))
INSERT INTO @silver
SELECT CustomerID, COUNT(*) numberOfOrders, SUM(SubTotal) transactionsAmount, 'Srebrna' color
FROM Sales.SalesOrderHeader
WHERE CustomerID NOT IN (SELECT customerID FROM @platinum) AND
CustomerID NOT IN (SELECT customerID FROM @gold)
GROUP BY CustomerID
HAVING COUNT(*) >= 5




INSERT INTO dbo.KartyLojalnosciowe
SELECT FirstName, LastName, numberOfOrders, transactionsAmount, color
	FROM Sales.Customer JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID
	JOIN @platinum ON [@platinum].customerID = Customer.CustomerID
	UNION ALL
	SELECT FirstName, LastName, numberOfOrders, transactionsAmount, color
	FROM Sales.Customer JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID
	JOIN @gold ON [@gold].customerID = Customer.CustomerID
	UNION ALL
	SELECT FirstName, LastName, numberOfOrders, transactionsAmount, color
	FROM Sales.Customer JOIN Person.Person ON Person.BusinessEntityID = Customer.PersonID
	JOIN @silver ON [@silver].customerID = Customer.CustomerID

SELECT * FROM dbo.KartyLojalnosciowe


DROP TABLE dbo.KartyLojalnosciowe