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