SQL Server Magazine

Last month's question asked: Using the FoodMart Sales cube, what are the unit sales of the states in the United States for each of the most recent 3 months that have data? The query in Listing A is the answer.

Multiple solutions will work, but I chose calculated sets because this solution demonstrates the use of sets and how to index into a set to return a particular set item. The first set, [Recent Months], uses the filter function to collect all the months that have data. The second set, [Recent Month], uses the item property to index into the first set to retrieve the most recent month. [Recent Month] is a set containing a single month.

This query displays the most recent three months in reverse order on the columns. It uses the item property to retrieve the first item in the [Recent Month] set. Then it uses the Lag function to retrieve the adjacent months.

End of Article