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

Here, the tables of the geography dimension are joined twice with the fact
table for obtaining the countries of the customer and the supplier.
Query 6.3. Monthly sales by customer state compared to those of the
previous year.
CREATE FUNCTION MonthYear (@Month INT, @Year INT)
RETURNS CHAR(14) AS
BEGIN
DECLARE @Date CHAR(10);
SET @Date = CAST(@Year AS CHAR(4)) + ' - ' +
CAST(@Month AS CHAR(2)) + ' - ' + ' 01 ' ;
RETURN(Datename(month,@Date) + '' +
CAST(@Year AS CHAR(4)));
END
WITH MonthlySalesState AS (
SELECT S.StateName, T.MonthNumber, T.Year,
SUM(SalesAmount) AS SalesAmount
FROM SalesF,CustomerC,CityY,StateS,TimeT
WHERE F.CustomerKey = C.CustomerKey AND
C.CityKey = Y.CityKey AND
Y.StateKey = S.StateKey AND
F.OrderDateKey = T.TimeKey
GROUP BY S.StateName, T.Year, T.MonthNumber )
SELECT M1.StateName,
dbo.MonthYear(M1.MonthNumber,M1.Year) AS Month,
FORMAT(M1.SalesAmount, ' $###,##0.00 ' ),
FORMAT(M2.SalesAmount, ' $###,##0.00 ' )ASPreviousYear
FROM MonthlySalesState M1 LEFT OUTER JOIN
MonthlySalesState M2 ON M1.StateName = M2.StateName
AND M1.MonthNumber = M2.MonthNumber AND
M1.Year-1 = M2.Year
ORDER BY M1.StateName, Month
In this query, we define a MonthYear function that concatenates a month
and a year for a more user-friendly display. In the WITH clause, we define a
common table expression (see Chap. 2 ) which computes the monthly sales by
state. In the query, the temporary table is joined twice to obtain the sales of
a month and the previous month. A left outer join is used for displaying a
null value in case there are no sales for the previous month.
Query 6.4. Monthly sales growth per product, that is, total sales per
product compared to those of the previous month.
WITH MonthlySalesProd AS (
SELECT P.ProductName, T.MonthNumber AS Month, T.Year,
SUM(SalesAmount) AS SalesAmount
FROM Sales S, Product P, Time T
WHERE S.ProductKey = P.ProductKey AND
S.OrderDateKey = T.TimeKey
GROUP BY P.ProductName, T.Year, T.MonthNumber ),
MonthlySalesProdComp AS (
SELECT M1.ProductName,