Category: T-SQL

Itzik Ben-Gan has a new challenge for the new year:

Packing intervals is a classic SQL task that involves packing groups of intersecting intervals to their respective continuous intervals. In mathematics, an interval is the subset of all values of a given type, e.g., integer numbers, between some low value and some high value. In databases, intervals can manifest as date and time intervals representing things like sessions, prescription periods, hospitalization periods, schedules, or numeric intervals representing things like ranges of mile posts on a road, temperature ranges, and so on.

An example for an interval packing task is packing intervals representing sessions for billing purposes. If you conduct a web search of the term packing intervals SQL, you’ll find scores of articles on the subject, including my own.

Read on for more information about this challenge and one solution to it.

Daniel Hutmacher builds a few functions:

As part of spending waaaaaay to much time trying to solve the 2023 Advent of Code challenges, I came across multiple instances where I had to dust off some old math that I hadn’t paid attention to since I went to school back in the 90ies.

So for my own convenience, and yours, I’ve built functions for some common math that you might perhaps encounter at some point. I found this whole experience to be a great way to familiarize myself with a lot of the new functionality in SQL Server 2022, including GENERATE_SERIES(), LEAST(), GREATEST() and more. The Github repo contains a SQL Server 2019 version where I’ve built drop-in versions of the 2022 functions, but they probably won’t perform as well as the built-in stuff.

Click through for demonstrations of determining whether something is a prime number, finding the greatest common divisor and least common multiple, factorization, factorials, and even a bit of combinatorics.

Kevin Wilkie got to day 9:

Today, our elves are working hard on having us come up with the next number in a sequence. Thankfully, they do give us a few good examples to walk through on how they would like us to do it. And with that, it’s off to the races!

Click through for Kevin’s answer.

Kevin Wilkie continues a series on this year’s advent of code. Day 8:

To make life a little simpler for all of us, I used a variable that I just pasted my instructions into. In my case, I called it @FollowThis allowing it to be up to 300 characters in length. Hopefully, no one’s instructions list is over that length – if so you’ll need to make that adjustment.

I also created a variable called @BackUpOfFollowThis that is strictly a copy of the original @FollowThis variable. (No muss, no fuss)

Once I place all of my inputs into the dbo.AOCDay8 table, we can easily start slicing and dicing.

And the more complex form for day 8:

First, we find out that we’re now starting with multiple positions – anything that ends with an A – and finishing anywhere that ends in a Z.

To make this happen, we’re going to want to create another table that will have all of our positions that end with the letter A.

Click through for Kevin’s solutions.

Kevin Wilkie continues the 2023 advent of code. Part 1 of day 7 covers card hands:

I have my version of the Day 7 data in my handy dandy table called AOCDay7. This time we’re being asked to figure out what kinds of Camel hands are given to us and then told to rank all of them.

Let’s start by aggregating and analyzing the data a little bit.

Part 2 makes jokers wild:

This time, our wonderful friends the elves have decided that they don’t like Jacks but they do like Jokers. So, there are a few changes to our code that will need to be made:

Click through for Kevin’s solutions to these challenges.