Voice of the DBA

Author: way0utwest

  • Daily Coping 19 May 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to try a vegetarian dish for a meal today.

    My oldest is a pseudo vegetarian. It’s a social issue with him and he sticks to it very well. Once in awhile I’m making some barbacoa or carnitas and tempt him, but not too often. Usually I try to accommodate him if I’m cooking and ensure there are good meatless options. He also picks vegetarian restaurants when he celebrate his birthday, so I’ve gotten good at cooking some meals without meat.

    Today I went looking for some new things and came upon this: Vegan Roasted Sweet Potato Salad. It looks good, has things the family likes, and is gluten free, which works well for my daughter. I roasted the potatoes late one afternoon while finishing work. Luckily I can hear the timer beep to get this one. I chopped a few vegetables and then assembled the salads on plates, expecting everyone to come in.

    I did forego the custom salad dressing as I didn’t feel like messing with the blender and cleaning it on this day. This worked out well, overall, as everyone was busy and late for dinner. I’d assembled things at 6, but it wasn’t until 730 that my wife came in from the barn.

    It was a nice, light dinner for all of us, and quite taste. The dressing (jalapeno lime vinaigrette) was spicy, though not sure I’d have been better making it from scratch.

  • Daily Coping 18 May 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag. 

    Today’s tip is to pause your work every hour and stand up to stretch.

    I don’t get into the zone too often, but when I do I’m glad. However, I do bundle together tasks and try to get a number of related items done at once. Switching tasks seems like a good time to pause and stretch, but I don’t do that enough.

    I’m making it a point to do better today. Hopefully from now on, but for today. When I switch tasks today, I’m standing up to do a few yoga stretches and deep breathing. As a backup, I’m setting a repeating 60 minute timer on my desktop to remind me.

    That won’t work past today, as it will get annoying, but for a day, it might help me to think about this more often.

  • 2020 Advent of Code – Day 5

    This series looks at the Advent of Code challenges.

    As one of my goals, I’m working through challenges. This post looks at day 5. I’m going to do this one in Python here, though I did solve it in other languages in my repo.

    Part 1

    This is an interesting problem, and one that’s simpler than it appeared at first. I started down the path of some hash bucket thing, moving to calculate rows before I got to the end and realized this is really a binary problem.

    As a result, after I loaded the data, I started here:

    SELECT 
       (SUBSTRING(d.SeatCode, 1, 1) * 64) +
       (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
       (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
       (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
       (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
       (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
       (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
       (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
       (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
       (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
      FROM dbo.Day5 AS d

    Here you can see I broke this into two binary sections. The first 7 characters get you a row code from 0 to 127. The last 3 values get you a 0 to 7 value. I should have been clued in when I saw the 0s here. In any case, this gets me the two binary values.

    The seat code is the row multiplied by 8 and then adding the seat. I took the above query, wrapped it with a CTE and then ordered by seat codes. This gave me the highest value, which solved the problem.

    WITH cteAirplane( ROW, seat)
    
AS
    
(SELECT 
       (SUBSTRING(d.SeatCode, 1, 1) * 64) +
       (SUBSTRING(d.SeatCode, 2, 1) * 32 ) +
       (SUBSTRING(d.SeatCode, 3, 1) * 16 ) +
       (SUBSTRING(d.SeatCode, 4, 1) * 8 ) +
       (SUBSTRING(d.SeatCode, 5, 1) * 4    ) +
       (SUBSTRING(d.SeatCode, 6, 1) * 2    ) +
       (SUBSTRING(d.SeatCode, 7, 1) * 1    ) AS row,
       (SUBSTRING(d.SeatCode, 8, 1) * 4    )  +
       (SUBSTRING(d.SeatCode, 9, 1) * 2    )  +
       (SUBSTRING(d.SeatCode, 10, 1) * 1    )  AS seat
      FROM dbo.Day5 AS d
      --ORDER BY row desc
      )
      SELECT (row * 8)+seat AS seatID
      FROM cteAirplane
      ORDER BY seatID DESC

    Part 2

    The second part is a different problem. Now I need the seat codes, but I’m looking for a gap here. Meaning a missing seat code.

    I decided to use LAG here. I altered my first CTE to calculate the seat code directly rather than returning the row and seat. Then I added this CTE:

    cteValues (SeatID, diff)
    
AS
    
(
    
SELECT seatid, SeatID - LAG(SeatID,1) OVER (ORDER BY SeatID) AS diff
    
FROM cteAirplane
    
)

    This CTE found the difference between each subsequent Seat codes using the OVER() clause. My final query was looking for a diff > 1, which returned 1 row. That was the answer.

  • A Work Conference

    I like the idea of a work conference. We’re actually having one at Redgate this week, though it’s all virtual. I’d like an in person one, and it sounds like that will happen at Atlassian four times a year when they require employees to be in the office. That’s it.

    Four times a year.

    They are going all in on work from home, allowing employees to be in any place that Atlassian has a legal presence for work. As someone that’s dealt with this, it is a hassle for a company doesn’t have any setup for an employee in a new locale, especially in the US. So I understand them limiting employees to places where the company has a corporate entity. Still, that’s a lot of places for Atlassian.

    Many Atlassian employees do expect to go to offices about half the time, but they won’t be required to do so. I think there are lots of their staff that want to be able to go to an office, at least some time.  I do wonder how they will handle meetings and collaboration with people being able to move their schedule around and be in or out of the office. At Redgate we’ve said remote first, but sitting at my desk for a meeting with one person that’s at their desk in the same room and one person that is remote feels strange.

    Getting everyone in a region together a few times a year is a really interesting idea. It will feel like a SQL Saturday or other event when you know everyone will show up. I can see this generating some excitement. Especially if those days are different from normal workdays.

    It’s something I think I’d be interested in, and perhaps excited by. I think I’ll suggest each Redgate office have a few days a year, and then see if I can get invited to each office on those days. Fingers crossed that it happens.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.