Stairway to Advanced T-SQL Level 5: Turning Dat a On Its Side Using PIVOT Operator (2022-01-12)

Daily Coping Tip

Write a short list of 3 things you feel grateful for and why

I also have a thread at SQLServerCentral dealing with coping mechanisms and resources. Feel free to participate.

For many of you out there working in a new way, I'm including a thought of the day on how to cope in this challenging time from The Action for Happiness Coping Calendar. My items will be on my blog, feel free to share yours.

Coding for the Future

I posted a note on Twitter about some code. In this case, it was the start of a for loop, but with two things in it that I didn't like. The code is shown here:

for ($i = 2011; $i <= 2021; $i++) {

Both the starting point and the ending condition were problematic for me. This code is used in a scheduling application, to populate a dropdown that I used regularly. It's always worked, since about 2017 or 2018, and I had no reason to even think about the algorithm. I assumed the code was something like this:

for ($i = 2011; $i <= year(); $i++) {

In fact, when I had issues on 27 Dec, I assumed that things were coded to the current year and I'd be able to schedule things on Sunday night when I got home. Instead, things still didn't work the first few days of 2022 and I had to get someone to come in from holiday to fix the code. Now it looks back a few years (likely unnecessary) and looks forward one year, as it should.

I don't know why a developer would bother coding anything like this. Get the need to perhaps test something expediently and limit choices, but hard-coding most anything is a poor idea. There ought to be some logic in how or why you choose values, and that logic often can be expressed in code that adapts to situations. I certainly could have lived with only the current year being available, though that still doesn't think through the problem space.

I've seen lots of simple bugs in code over the years that are similar to this one. These often occur because the developers haven't thought through how code might need to adapt to slightly changing situations. Something like a change in the year, or maybe the addition of a time slot for scheduling or a new location added, even a new tax rate. Often I find developers think they have considered the problem given to them, but a little too tightly. They haven't thought about how their code might change if the user requesting the feature has made a mistake.

Writing code that solves a problem and allows for adaptation to likely scenarios is a bit of an art. It can be hard to do, but great developers assume users will need some changes and plan for this in their solution. They also assume users make mistakes in their specifications and they don't hard code anything.

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums