Wrapping up

1. Wrapping up

Congratulations! You have just completed the course Time Series Analysis in SQL Server. Let's quickly hit the highlights of what you've learned.

2. Working with dates

In chapter 1, we looked at different ways to work with dates and times. We learned how to use date functions and also combine DATEADD() with DATEDIFF() to round off to the nearest minute or hour. We learned how to use CAST(), CONVERT(), and FORMAT() to turn dates into strings for reporting, but we also learned that FORMAT() can be an order of magnitude slower than the others. The biggest takeaway from chapter 1 is calendar tables. I highly recommend keeping a calendar table around; it comes in handy more often than you'd think.

3. Building dates

Chapter 2 was all about building dates from strings and from component parts. The CAST(), CONVERT(), and PARSE() functions give you several methods to convert strings into dates, even when the string uses a different locale from your computer. That said, TRY_CAST(), TRY_CONVERT(), and TRY_PARSE() are just as fast and are also type-safe, meaning you get a NULL value instead of an error when the string does not parse to a date. We also learned how SQL Server lets us keep track of time zones using offsets and how to translate dates between time zones and changing time zones altogether.

4. Time-based aggregates

Chapter 3 was all about aggregations. We reviewed aggregation functions like COUNT(), MIN(), MAX(), and more. We also reviewed statistical aggregate functions like AVG(), STDEV(), and VAR(). We also reviewed using ROLLUP, CUBE, and GROUPING SETS to aggregate data.

5. Common (and uncommon) time series problems

Chapter 4 was all about handling common--as well as a few uncommon--time series problems. We saw how important window functions are, both for ranking functions like ROW_NUMBER() and RANK(), but also for aggregate functions like SUM() or AVG(). With those window functions, we learned how to calculate running totals and moving averages over windows of data. We also learned how to look backward and forward in time with LAG() and LEAD(), respectively. Finally, we learned how to calculate the maximum level of overlap, important for discovering concurrency in data sets.

6. Grazie!

I hope you found this course useful and informative. Thank you for your dedication.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.