SET STATISTICS IO, TIME ON

CREATE TABLE #TotalItems
	(StockItemId INT PRIMARY KEY, 
	Quantity INT);

CREATE TABLE #MaxQuantityPerCustomer 
	(CustomerId INT PRIMARY KEY, 
	Quantity INT);
 
DECLARE @dateFrom DATE, @dateTo DATE

SET @dateFrom = DATEADD(yy, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0));
SET @dateTo = DATEADD(yy, -2, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0));
SET @dateTo = DATEADD(dd, -1, @dateTo);

SELECT @dateFrom, @dateTo

INSERT INTO #TotalItems
(StockItemId, Quantity)
SELECT L.StockItemID, SUM(L.Quantity)
FROM Sales.InvoiceLines AS L
	JOIN Sales.Invoices AS I
		ON I.InvoiceID = L.InvoiceID
		AND I.InvoiceDate BETWEEN @dateFrom AND @dateTo
GROUP BY L.StockItemId;

INSERT INTO #MaxQuantityPerCustomer
(CustomerId, Quantity)
SELECT I.CustomerID, MAX(L.Quantity) AS Q
FROM Sales.InvoiceLines AS L
	JOIN Sales.Invoices AS I
		ON I.InvoiceID = L.InvoiceID
		AND I.InvoiceDate BETWEEN @dateFrom AND @dateTo
GROUP BY I.CustomerID;

WITH Invoices AS 
(SELECT Inv.InvoiceDate, Inv.BillToCustomerID, 
	Inv.CustomerID, Inv.SalespersonPersonID, Inv.OrderID, Details.StockItemID 
FROM Sales.Invoices AS Inv
	JOIN Sales.InvoiceLines AS Details
		ON Inv.InvoiceID = Details.InvoiceID)
SELECT Client.CustomerName, 
	Inv.InvoiceID, 
	Inv.InvoiceDate, 
	Item.StockItemName, 
	Inv.Quantity, 
	Totals.Quantity AS TotalItems,
	MaxCustomers.Quantity AS MaxByClient,
	PayClient.CustomerName AS BillForCustomer,
	Pack.PackageTypeName,
	People.FullName AS SalePerson,
	OrdLines.PickedQuantity
FROM Invoices AS Inv
	JOIN Sales.Customers AS Client 
		ON Client.CustomerID = Inv.CustomerID
	JOIN Application.People AS People
		ON People.PersonID = Inv.SalespersonPersonID
	JOIN Sales.Customers AS PayClient 
		ON PayClient.CustomerID = Inv.BillToCustomerID
	JOIN Warehouse.StockItems AS Item 
		ON Item.StockItemID = Inv.StockItemID
	JOIN Sales.Orders AS Ord 
		ON Ord.OrderID = Inv.OrderID
	JOIN Sales.OrderLines AS OrdLines
		ON OrdLines.OrderID = Ord.OrderID
		AND OrdLines.StockItemID = Item.StockItemID
	JOIN Warehouse.PackageTypes AS Pack
		ON Pack.PackageTypeID = OrdLines.PackageTypeID
	JOIN #TotalItems AS Totals 
		ON Totals.StockItemID = Inv.StockItemID
	JOIN #MaxQuantityPerCustomer AS MaxCustomers 
		ON MaxCustomers.CustomerId = Inv.CustomerID
WHERE Inv.InvoiceDate BETWEEN @dateFrom AND @dateTo AND
	 OrdLines.PickedQuantity > 0
ORDER BY TotalItems DESC, Quantity DESC, CustomerName;


DROP TABLE #MaxQuantityPerCustomer;
DROP TABLE #TotalItems;
