Stephen Balkum

Agile Enthusiast, Leader, and Developer

So You Want to Learn SQL… Final Session

no comment

As mentioned in the previous session, this is the last issue of this series.  Even though I could continue with more rarely used SQL features, I feel the content presented meets the stated goal.  Here are the final homework answers.

Homework Answers

1) List the two-week rolling total quantity by territory for the current period and 3 prior periods by week. The result set should have three columns: Period Start Date, Territory Name, and Total Quantity.

SELECT w.startdate, t.Name, SUM(od.OrderQty) AS Quantity
	FROM Sales.SalesOrderHeader o
	JOIN Sales.SalesOrderDetail od ON od.SalesOrderID = o.SalesOrderID
	JOIN Sales.SalesTerritory t ON t.TerritoryID = o.TerritoryID
	JOIN (
		SELECT	DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'StartDate',
			DATEADD(dd, 13, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)) as 'EndDate'
		UNION ALL
		SELECT	DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, 6, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
		UNION ALL
		SELECT	DATEADD(dd, -15, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, -1, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
		UNION ALL
		SELECT	DATEADD(dd, -22, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0)),
			DATEADD(dd, -8, DATEADD(ww, DATEDIFF(ww, 0, '8/1/2008'), 0))
	) W ON W.StartDate <= o.OrderDate AND w.EndDate > o.OrderDate
	GROUP BY w.startdate, t.Name
	ORDER BY w.StartDate, t.Name

2) List the total sales for each day of the week by territory for July 2008. The columns in the result set should be: Territory, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday. Do not display the territory if it had zero sales all week.

SELECT	t.Name AS Territory,
		ISNULL(Sunday.Sales, 0) AS Sunday,
		ISNULL(Monday.Sales, 0) AS Monday,
		ISNULL(Tuesday.Sales, 0) AS Tuesday,
		ISNULL(Wednesday.Sales, 0) AS Wednesday,
		ISNULL(Thursday.Sales, 0) AS Thursday,
		ISNULL(Friday.Sales, 0) AS Friday,
		ISNULL(Saturday.Sales, 0) AS Saturday
	FROM Sales.SalesTerritory t
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 1
			GROUP BY o.TerritoryID
	) Sunday ON Sunday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 2
			GROUP BY o.TerritoryID
	) Monday ON Monday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 3
			GROUP BY o.TerritoryID
	) Tuesday ON Tuesday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 4
			GROUP BY o.TerritoryID
	) Wednesday ON Wednesday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 5
			GROUP BY o.TerritoryID
	) Thursday ON Thursday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 6
			GROUP BY o.TerritoryID
	) Friday ON Friday.TerritoryID = t.TerritoryID
	LEFT JOIN (
		SELECT  o.TerritoryID,
				SUM(o.TotalDue) AS Sales
			FROM Sales.SalesOrderHeader o
			WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
				AND DATEPART(dw, o.OrderDate) = 7
			GROUP BY o.TerritoryID
	) Saturday ON Saturday.TerritoryID = t.TerritoryID
	WHERE Sunday.Sales IS NOT NULL
		OR Monday.Sales IS NOT NULL
		OR Tuesday.Sales IS NOT NULL
		OR Wednesday.Sales IS NOT NULL
		OR Thursday.Sales IS NOT NULL
		OR Friday.Sales IS NOT NULL
		OR Saturday.Sales IS NOT NULL
	ORDER BY t.Name

or

SELECT t.Name AS Territory,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 1 THEN o.TotalDue ELSE 0 END) AS Sunday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 2 THEN o.TotalDue ELSE 0 END) AS Monday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 3 THEN o.TotalDue ELSE 0 END) AS Tuesday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 4 THEN o.TotalDue ELSE 0 END) AS Wednesday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 5 THEN o.TotalDue ELSE 0 END) AS Thursday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 6 THEN o.TotalDue ELSE 0 END) AS Friday,
	SUM(CASE DATEPART(dw, o.OrderDate) WHEN 7 THEN o.TotalDue ELSE 0 END) AS Saturday
	FROM Sales.SalesTerritory t
	JOIN Sales.SalesOrderHeader o ON o.TerritoryID = t.TerritoryID
	WHERE o.OrderDate >= '7/1/2008' AND o.OrderDate < '8/1/2008'
	GROUP BY t.Name
	ORDER BY t.Name