Set-based approach to finding consecutive records

I’m sure I’ve written about this before, but it comes up so regularly in the newsgroups that I thought I’d write an answer here. A table (probably an audit table) exists with a field that stores a date signifying when a change was made. You need to return a result-set which has a row for each period of time between modifications. For example: CREATE TABLE QtyAdj ( …. , ModDate DATETIME, Qty INT ) –where I haven’t listed all the columns – there should obviously be a primary key on this. In fact, I’ll assume there is a field called … Continue reading Set-based approach to finding consecutive records

Faster binary converter – where do your solutions come from?

Mitch Wheat is a great guy. He really is. And he's clearly a mathematician. I mean, I did a half-major as part of my BSc, doing a handful of 3rd year Pure Maths subjects (which I got HDs in too – I wasn't just sleeping during class), but Mitch… he thinks like a mathematician. You can tell when he comes up with a solution like this for converting numbers to binary. Makes me wonder if this is something he came up with, or something he read in a book. Knowing Mitch, he came up with it himself. I'm in a … Continue reading Faster binary converter – where do your solutions come from?

Simple recursive CTE

I like CTEs. Mostly because it lets me have a derived table that I can refer to multiple times. I don't often use the recursive features of it. But when I do, I repeatedly feel amazed at the power available. Today I had a challenge to produce a nice SQL way of converting integers to binary.  So I pulled out the CTE. 🙂 with ctebins as(select num as num_orig, num as working_level, cast('' as varchar(max)) as binvalfrom nums_binunion allselect c.num_orig, c.working_level / 2, cast(c.working_level % 2 as varchar(max)) + c.binvalfrom ctebins cwhere c.working_level > 0)select num_orig, binvalfrom ctebinswhere working_level = … Continue reading Simple recursive CTE