A running (or cumulative) sum calculation can be useful in warranty scenarios.
/* This selects the total sales in the 12 most recent months. */ DEFINE Input AS SELECT DimDate_CalendarYear AS CalYear, DimDate_MonthNumberOfYear AS NumMonth, SUM(FactSales_SalesAmount) AS TotalSales FROM SaleState GROUP BY CalYear, NumMonth ORDER BY CalYear DESC, NumMonth DESC PAGE(0, 12); RETURN CumulativeSum AS SELECT one.CalYear AS CalYear, one.NumMonth AS NumMonth, SUM(many.TotalSales) AS TotalSales FROM Input one JOIN Input many ON ((one.CalYear > many.CalYear) OR (one.CalYear = many.CalYear AND one.NumMonth >= many.NumMonth) ) GROUP BY CalYear, NumMonth ORDER BY CalYear, NumMonth
In the example, the words "one" and "many" are statement aliases to clarify the roles in this many-to-one self-join. Looking at the join condition, you can think of this as, for each (one) record, create multiple records based on the (many) values that match the join condition.