Data Analytics: Exploiting the Data Warehouse - Data Warehouse Systems: Design and Implementation - page 371

FROM Sales S, Time T
WHERE S.OrderDateKey = T.TimeKey AND
DATEPART(yy, T.Date)=1997 AND
DATEPART(mm, T.Date)=4 ),
MonthlySalesApr1998 AS (
SELECT SUM(S.SalesAmount) AS TotalSales
FROM Sales S, Time T
WHERE S.OrderDateKey = T.TimeKey AND
DATEPART(yy, T.Date)=1998 AND
DATEPART(mm, T.Date)=4 )
SELECT MS.TotalSales, LYMS.TotalSales AS LY TotalSales,
MS.TotalSales / LYMS.TotalSales AS Percentage
FROM MonthlySalesApr1998 MS, MonthlySalesApr1997 LYMS

The above query defines two temporary tables computing, respectively, the
monthly sales for April 1997 and April 1998. Each of these tables results in
a single line. Then, a cross join of the two temporary tables is performed in
the main query to obtain both values in a single line.
Below, we analyze the query for the center left chart, which shows the
shipping costs with respect to the total sales by month:

SELECT DATEPART(yy, T.Date) AS Year, DATEPART(mm, T.Date) AS Month,
SUBSTRING(DATENAME(MM, T.Date), 1, 3) AS MonthName,
SUM(S.SalesAmount) AS TotalSales, SUM(S.Freight) AS TotalFreight,
SUM(S.Freight) / SUM(S.SalesAmount) AS Percentage
FROM Sales S, Time T
WHERE S.OrderDateKey = T.TimeKey AND NOT
( DATEPART(yy, T.Date) = 1998 AND DATEPART(mm, T.Date) = 5 )
GROUP BY DATEPART(yy, T.Date), DATEPART(mm, T.Date),
DATENAME(mm, T.Date)
ORDER BY Year, Month, DATENAME(mm, T.Date)

In this query, we compute the total sales and the total freight cost by month,
as well as the percentage between the two. As before, we exclude the values
from May 1998.
The gauge in the center right of Fig. 9.16 shows the percentage of shipping
costs with respect to the total sales from January to April 1998. The range
of the gauge (shown at the right of the scale) reflects the KPI used for
monitoring shipping costs, targeted at remaining below 5% of the sales
amount. The corresponding query is given next:

SELECT SUM(S.SalesAmount) AS TotalSales, SUM(S.Freight) AS TotalFreight,
SUM(S.Freight) / SUM(S.SalesAmount) AS Percentage
FROM Sales S, Time T
WHERE S.OrderDateKey = T.TimeKey AND DATEPART(yy, T.Date) = 1998 AND
DATEPART(mm, T.Date) > = 1 AND DATEPART(mm, T.Date) < =4

Finally, the query for the bottom table showing the three least performing
selling employees as of April 1998 is given next:

WITH Quota1998 AS (
SELECT S.EmployeeKey, SUM(S.SalesAmount) * 1.05 AS Quota