Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 220

WITH OrderAgg AS (
SELECT OrderNo, OrderDateKey,
SUM(SalesAmount) AS SalesAmount
FROM Sales
GROUP BY OrderNo, OrderDateKey )
SELECT dbo.MonthYear(MonthNumber,Year) AS Month,
COUNT(OrderNo) AS NoOrders,
FORMAT(SUM(SalesAmount), ' $###,##0.00 ' ) AS SalesAmount,
FORMAT(AVG(SalesAmount), ' $###,##0.00 ' )ASAvgAmount
FROM OrderAgg O, Time T
WHERE O.OrderDateKey = T.TimeKey
GROUP BY Year, MonthNumber
ORDER BY Year, MonthNumber
In the temporary table, we compute the total sales amount of each order.
Notice that we also need to keep the key of the time dimension, which will
be used in the main query for joining the fact table and the time dimension
table. Then, by grouping the tuples by year and month, we can compute the
aggregated values requested.
Query 6.15. For each employee, total sales amount, number of cities, and
number of states to which she is assigned.
SELECT FirstName + '' + LastName AS FullName,
FORMAT(SUM(SalesAmount) / COUNT(DISTINCT CityName),
' $###,##0.00 ' )ASTotalSales,
COUNT(DISTINCT CityName) AS NoCities,
COUNT(DISTINCT StateName) AS NoStates
FROM Sales F, Employee E, Territories T, City C, State S
WHERE F.EmployeeKey = E.EmployeeKey AND
E.EmployeeKey = T.EmployeeKey AND
T.CityKey = C.CityKey AND C.StateKey = S.StateKey
GROUP BY FirstName + '' + LastName
ORDER BY FirstName + '' + LastName
Recall that the Territories table captures the many-to-many relationship
between employees and cities. Thus, the above query makes the join of the five
tables and then groups the result by employee. Then, in the SELECT clause
we sum the SalesAmount measure and divide it by the number of distinct
CityName assigned to an employee in the Territories table. This solves the
double-counting problem to which we referred in Sect. 4.2.6 .
Suppose now that the Territories table have an additional attribute
Percentage stating the percentage of time an employee is assigned to each
city. In this case, the query above would be as follows:
SELECT FirstName + '' + LastName AS FullName,
FORMAT(SUM(SalesAmount) * T.Percentage,
' $###,##0.00 ' )ASTotalSales,
COUNT(DISTINCT CityName) AS NoCities,
COUNT(DISTINCT StateName) AS NoStates
FROM Sales F, Employee E, Territories T, City C, State S
WHERE . . .