USE [AdventureWorks2017];

SELECT DISTINCT(YEAR([OrderDate])) AS 'Years'
FROM [Sales].[SalesOrderHeader]
ORDER BY 'Years';

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

SELECT YEAR([OrderDate]) AS 'Year',
	   MONTH([OrderDate]) AS 'Month',
	   COUNT(*) AS 'Amount of orders'
FROM [Sales].[SalesOrderHeader]
WHERE MONTH([OrderDate]) = 5
GROUP BY YEAR([OrderDate]),
	     MONTH([OrderDate])
ORDER BY YEAR([OrderDate]);

SELECT  [Sales].[Customer].[CustomerID] AS 'Client ID',
		CONCAT([LastName],', ',[FirstName]) AS 'Name' ,
		COUNT(*) AS 'Amount of orders'
FROM [Sales].[Customer]
INNER JOIN [Person].[Person]
	ON ([BusinessEntityID] = [CustomerID])
LEFT JOIN [Sales].[SalesOrderHeader]
	ON [Sales].[SalesOrderHeader].[CustomerID]  = [Sales].[Customer].[CustomerID]
GROUP BY [Sales].[Customer].[CustomerID],
		 [FirstName],
		 [LastName]
HAVING COUNT(*) >= 25
ORDER BY COUNT(*) DESC ;


SELECT YEAR([OrderDate]) AS 'Year',
	   MONTH([OrderDate]) AS 'Month',
	   DAY([OrderDate]) AS 'Day',
	   COUNT(*) 'Amount of orders',
	   SUM([SubTotal]) 'Total Price'
FROM [Sales].[SalesOrderHeader]
LEFT JOIN [Sales].[SalesOrderDetail]
	ON [Sales].[SalesOrderDetail].[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID]
GROUP BY YEAR([OrderDate]),
		 MONTH([OrderDate]),
		 DAY([OrderDate])
ORDER BY YEAR([OrderDate]),
		 MONTH([OrderDate]),
		 DAY([OrderDate]);