/*
	Display the week number for an order's month, and the week number of the last day of an 
	order's month (where a week starts on a Monday)
*/


USE AdventureWorks2014
GO

SELECT CONVERT(VARCHAR(10),OrderDate,103) AS 'Order Date', 
	CASE 
		WHEN DAY(OrderDate)/7 = 0
			THEN DAY(DATEADD(DAY,-DAY(OrderDate),OrderDate))/7 + 1
		WHEN DATEPART(WEEKDAY, DATEADD(DAY,-DAY(OrderDate),OrderDate)) > 1
			THEN DAY(OrderDate)/7
		WHEN DATEPART(WEEKDAY, DATEADD(DAY,-DAY(OrderDate),OrderDate)) = 1
			THEN DAY(OrderDate)/7 + 1
	END AS 'Week Number Of Order''s Month',
	CONVERT(VARCHAR(10),DATEADD(DAY, -DAY(OrderDate) ,DATEADD(MONTH, 1, OrderDate)),103) 
			AS 'Last Day Of Order''s Month',
	DATENAME(WEEKDAY, DATEADD(DAY, -DAY(OrderDate) ,DATEADD(MONTH, 1, OrderDate))) 
			AS 'Last Weekday Of Order''s Month'
FROM Sales.SalesOrderHeader