Press "Enter" to skip to content

Category: Syntax

Continuing the Advent of Code

Published 2023-12-08 by Kevin Feasel

Kevin Wilkie has been busy. Here’s Day 1 Part 2:

Today, I want to review part 2 of Day 1 of the Advent of Code series. Hopefully, everyone was able to complete part 1 with no troubles, or at least understood what I did to get there.

For part 2, they added a slight wrinkle to the part 1 puzzle. They spell out the numbers into actual words! How do you find them as well as find the numbers? Well, my friend, let’s go through that process, shall we?

After that is Day 2 Part 1:

On day 2, we are asked to gather data from a series of games and to see which of those are possible given a specific number of dice for a few colors. Fun times!

And then there’s Day 2 Part 2:

Today, we’ll be working on the next in the series using the data and processes that we found yesterday in Day 2 Part 1 – found here.

Thankfully, we were smart when we began working through the data and we have the data for each of our dice in separate tables, so breaking the data apart has definitely paid off! Now we can do just a little bit of work with the data from yesterday and we’ll be ready to give the results!

Comments closed

Joining on Overlapping Date Ranges in T-SQL

Published 2023-12-07 by Kevin Feasel

Daniel Hutmacher crosses the streams:

You can get into a situation where you have two tables with values associated with date ranges. What’s worse, those date ranges don’t necessarily have to align, which can make joining them a seemingly complex task, but it is surprisingly simple when you learn how to think of overlapping date ranges, along with this relatively simple T-SQL join pattern.

This problem gets even more challenging if you have the possibility of multiple overlaps and you want to find the combination with the biggest overlap for each individual item.

Comments closed

Grouping By Column Alias

Published 2023-12-04 by Kevin Feasel

Aaron Bertrand wants a feature:

GROUP BY queries can become overly convoluted if your grouping column is a complex expression. Because of the logical processing order of a query, you’re often forced to repeat such an expression since its alias can’t be used in the GROUP BY clause.

Oracle recently solved this in their 23c release by adding the ability to GROUP BY column_alias. This is such simple but powerful syntax, and I’m hoping we can get SQL Server to follow Oracle’s lead.

This would be a pretty nice feature. Admittedly, the workarounds aren’t that difficult, but this would be a nice quality of life update.

Comments closed

Combining Window Functions and GROUP BY

Published 2023-11-27 by Kevin Feasel

Andy Brownsword aggregates some data:

We revisited window functions last week for T-SQL Tuesday. As we’re in that area there’s another example I thought was worth exploring. Can we group data whilst applying window functions in the same query?

Andy comes up with a final query that works perfectly fine, but there’s actually an easier answer in terms of code readability: the DISTINCT operator.

SELECT DISTINCT FinancialQuarter, QuarterAvg = AVG(SalesValue) OVER (PARTITION BY FinancialQuarter), YearAvg = AVG(SalesValue) OVER (PARTITION BY FinancialYear)
FROM #MonthlySales;

The FinancialQuarter column is unique so we can perform the window operation for averaging sales value over financial quarter and then by financial year. To remove the “duplicate” rows, we run DISTINCT and get the same results.

That said, the execution plan for this is a little more complex, as we have to go through a lazy spool on two separate occasions rather than the one that Andy’s solution comes up with. For sufficiently large datasets, that could make a difference, so as usual, choose the option that works better for your situation.

Comments closed

T-SQL Tuesday 168 Round-Up

Published 2023-11-24 by Kevin Feasel

Steve Jones lagged a bit:

I didn’t get much of a chance to check out the posts as I was at the PASS Data Community Summit, but I came home and started to work through them.

This was the 8th one I’ve hosted, which makes sense as I’ve taken over managing the party from Adam Machanic and there have been a few places I’ve had to fill in for missing hosts. In any case, here’s the roundup. I’m going in order of the comments as I see them on the blog.

Click through for this month’s list of entrants.

Comments closed

Last Observation Carried Forward in SQL Server 2022

Published 2023-11-16 by Kevin Feasel

Barney Lawrence shows off a nice enhancement to T-SQL in SQL Server 2022:

With SQL Server 2022 came a much requested additional feature added from the SQL standards – IGNORE_NULLS. You can probably guess what it does. Drop in IGNORE_NULLS after your function and you can blur the non null values over those gaps giving us results like this:

Read on for the pre-2022 version of the query and what it does, versus the version with IGNORE_NULLS specified. This small flag is extremely helpful in time series statistical analysis and I’m glad it’s in SQL Server now.

Comments closed

A Good Use of LEAD()

Published 2023-11-16 by Kevin Feasel

Chad Callihan gets the lead out:

Imagine we want to find gaps in post history for users. If a user is posting every day, we’re happy and can assume they’re happy. If they’re only posting once every few weeks, we want to investigate why that is.

Read on to see how you can do this with the LEAD() function.

Comments closed

Operations Auditing with LAG() and LEAD()

Published 2023-11-15 by Kevin Feasel

Deborah Melkin builds a report query:

I’ve been doing a lot of work recently where I have long running processes where I need to have visibility on where we are during the process as well as being able to provide a report we can use to find out if there were any errors, how long each step took, and other useful information that we can use later. My solution was to create an audit log table where I simply added a row before and after each step, recording the information I had available. When the processes are done, I create a query to run over the table returning the values I want to see.

Deb also throws in the only ordered set function in SQL Server, STRING_AGG().

Comments closed

Using the WINDOW Operator in SQL Server 2022

Published 2023-11-15 by Kevin Feasel

Steve Jones cuts out the duplication:

I am the host for T-SQL Tuesday this month, and I hope that a lot of people like the topic. This idea actually came to me earlier this year when I happened to see someone ask about a T-SQL problem and get an answer using a Window function. This person mentioned they hadn’t used the window function before, and I wondered how many people haven’t even tried using the OVER() clause with a window function.

Read on to see how you can use WINDOW to replicate window frames, which can be quite helpful for larger queries. It doesn’t change the performance profile but does make the code more readable.

Comments closed

Good Use Cases for Window Functions

Published 2023-11-15 by Kevin Feasel

Aaron Bertrand build a list:

When I first used window functions back in SQL Server 2005, I was in awe. I had always used inefficient self-joins to calculate things like running totals, and these really didn’t scale well with size-of-data. I quickly realized you could also use them for ranks and moving averages without those cumbersome self-joins, elaborate sub-queries, or #temp tables. Those all have their place, but window functions can make them feel old-school and dirty.

I’d also recommend learning more about the APPLY operator as well, as it can, depending on the circumstances, be even more effective than window functions (combined with common table expressions) for some of the use cases.

Comments closed