Wrapping up

Time Series Analysis in SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Working with dates

• Combine DATEADD() and DATEDIFF() to round dates and times.
• Format with CAST() and CONVERT() when performance matters. FORMAT() is useful but slow.
• Calendar tables are a valuable asset.

Building dates

• CAST(), CONVERT(), and PARSE() can all turn strings into dates.
• Use TRY_CAST(), TRY_CONVERT(), and TRY_PARSE() for safe date conversions.
• SWITCHOFFSET() and TODATETIMEOFFSET() are useful functions for working with offsets.

Time-based aggregates

• Aggregate functions include COUNT(), MIN(), MAX(), and SUM().
• Statistical aggregate functions include AVG(), STDEV(), VAR(), STDEVP(), and VARP().
• ROLLUP, CUBE, and GROUPING SETS allow you to refine your aggregations.

Common (and uncommon) time series problems

• Windows work over ranking functions (ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()) as well as aggregate functions (including statistical functions).
• Running totals are a use of SUM() over a window.
• Moving averages are a use of AVG() over a window.
• LAG() and LEAD() let us peek backward and forward in time.
• Pivot and transform date data to calculate concurrency.

Grazie!

Time Series Analysis in SQL Server