Expert T-SQL Window Functions in SQL Server 2019: The Hidden Secret to Fast Analytic and Reporting Queries

© Kathi Kellenberger, Clayton Groom, and Ed Pollack 2019

K. Kellenberger et al.Expert T-SQL Window Functions in SQL Server 2019https://doi.org/10.1007/978-1-4842-5197-3_10

10. Time Range Calculations

Kathi Kellenberger¹ , Clayton Groom² and Ed Pollack³

(1)

Edwardsville, IL, USA

(2)

Smithton, IL, USA

(3)

Albany, NY, USA

 

A very common reporting requirement is to produce totals by different ranges of time for comparison. Typical reports will 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 Power BI provide functions to navigate date hierarchies. With window functions in SQL Server 2012 or later, you can produce ...