CTE

Filling in Data Potholes Redux: Tally Tables vs CTEs

In A Previous Installment Re-Ducks … our heroine (that’s me) rediscovered CTEs, specifically in the recursive style. That was in my post “Filling in Data Potholes with Recursive CTEs.” To recap: I was working on a problem with gaps in temporal data. The basic scenario was: Imagine that you are writing a script that looks at data grouped by the minute. You notice that there are no rows for some minutes, and you’d like to display a value when that is the case, probably showing a count of zero. For the particular problem I was looking at, I was using small datasets and generating a list of all the valid dates with a recursive CTE performed well for me. From the Comments The best thing about blogging is not really sharing what you know: it’s getting to learn more. You get to learn from the process of writing the blog,…
Read More

Filling in Data Potholes with Recursive CTEs

Imagine that you are writing a script that looks at data grouped by the minute. You notice that there are no rows for some minutes, and you’d like to display a value when that is the case, probably showing a count of zero. In thinking about this problem this week, I spent some time getting to know CTEs (Common Table Expressions) again. And I came to the conclusion that I should spend much more time with them. Maybe I won’t end up using them all the time, but I should be looking at them regularly as options when I’m writing queries. Here’s the story of a handy way I found to work with this. Let’s create some data Our story starts with some data. It’s been lovingly scripted out, but it has a few holes. CREATE TABLE dbo.MyImperfectData ( ItemDate DATETIME2(0) , ItemCount SMALLINT ) GO INSERT dbo.MyImperfectData ( ItemDate,…
Read More
Menu