Time Range Calculations and Trends
A common reporting requirement is to produce totals by different ranges of time for comparison. Typical reports contain totals by month, quarter, and year, sometimes with comparisons to the same period in the prior year or for month-to-date or year-to-date totals. Products like SQL Server Analysis Services and PowerPivot provide functions to navigate date hierarchies. With window functions in SQL Server 2012 or later, you can produce the same calculations using the techniques provided earlier in this book.