Voice of the DBA

Tag: T-SQL

  • Logging Messages with Raiserror – #SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    I recently ran across some people discussing how to log some information in a script. One person was using PRINT, which I often use for quick checks, but someone else noted the RAISERROR works well, and you can customize messages.

    For example, I can have this type of script:

    DECLARE @d VARCHAR(20);

    -- do stuff
    

    SELECT @d = CAST( SYSDATETIME() AS VARCHAR(20));
    
RAISERROR('%s - something happened at this time', 0, 1, @d)

    This allows me to add information into an error message. I can certainly construct @d with other stuff and then use that in PRINT, but I could get out of order messages. If  I add NOWAIT, I can ensure my messages get returned immediately.

    There are lots of options with RAISERROR, which I still use in place of THROW at times. While I like THROW, I think it doesn’t always give me the options I want for error handling, such as logging to the Windows lots.

    SQL NewBlogger

    When I saw this, I realized that I didn’t know, or remember, some of the ins and outs of RAISERROR, so I spent a few minutes looking through docs and playing with the code. I then wrote this quick post to help me remember a bit more.

    Short and quick is a good way to structure posts. I didn’t walk about all the options or ways I can use things. I’ll do some of that in another post.

  • 2020 Advent of Code–Day 3

    This series looks at the Advent of Code challenges.

    As one of my goals, I’m working through challenges. This post looks at day 3.

    Part 1

    Day 3 was tough. The explanation isn’t great, at least, I didn’t get it at first. Essentially you have a map, and then you have some slope. The first part has a slope of right 3, down 1. If you move, assuming the upper left is (1,1), the next spot is 2, 4. That’s if you count going down as positive.

    Here’s the map:

    ..##.......
    
#...#...#..
    
.#....#..#.
    
..#.#...#.#
    
.#...##..#.
    
..#.##.....
    
.#.#.#....#
    
.#........#
    
#.##...#...
    
#...##....#
    
.#..#...#.#

    If I count each space, then there is a period (.) in this (2,4) space. If it’s a tree, then there is a hash (#) there. We are trying to count trees before we hit bottom.

    The trick I missed is that the map we’ve given repeats. It repeats to the right as often as needed to get to the bottom.

    This is really a coordinate problem, counting each down as we move, and repeating the map. The trick often in a short width here is to do the math to wrap around from the right to left if you run out of room.

    In SQL, I used a loop. I didn’t spend a ton of time, but couldn’t see a good way to avoid this as I need this to be readable, and I need this to keep working through the map. Here’s the code:

    WHILE @currentrow <= @rows
      BEGIN
        SELECT @currentcol += @right
        IF @currentcol > @width 
          SELECT @currentcol = @currentcol - @width
        SELECT @currentrow += @down;

       SELECT @trees = @trees + CASE
           WHEN SUBSTRING(dataval, @currentcol, 1) = '#' THEN 1
           ELSE 0
        end
         FROM day3
         WHERE rowkey = @currentrow
      END
    
SELECT @trees AS TreeCount

    I move, count the value if there’s as tree, and then continue moving through the next rows. The WHERE clause orients the rows.

    It worked. I go the right answer here.

    Part 2

    In part 2, this changes to checking a number of sloops and then multiplying the results together. In terms of my code, this is really a repetitive way of running the code again. I could have used different variables and checked multiple slopes at once, but I was busy.

    In python, I did something similar. I essentially calculated the next X and Y position, and then looped through the file. Each time the Y matched the current row, I checked for the matching #. If it matched, increment.

    Once I matched the correct Y, I incremented X and Y.

  • 2020 Advent of Code–Day 2

    This series looks at the Advent of Code challenges.

    As one of my goals, I’m working through challenges. This post looks at day 2.

    Part 1

    This problem is really a string processing issue. We get a long string that needs splitting into different parts. We have two numbers, a digit to check, and a password. In this part, we are looking to see if our check digit appears x number of times.

    For T-SQL, we can use Substring and charindex to split things. I had code like this:

    SELECT
                 SUBSTRING(datavalue, 1, CHARINDEX('-', datavalue) - 1) AS lowerbound
               , SUBSTRING(datavalue, CHARINDEX('-', datavalue) + 1, CHARINDEX(' ', datavalue) - CHARINDEX('-', datavalue) - 1) AS upperbound
               , SUBSTRING(datavalue, CHARINDEX(' ', datavalue) + 1, 1)
               , SUBSTRING(datavalue, CHARINDEX(':', datavalue) + 2, 50)
            FROM dbo.Day2 AS d

    From here, I can do some counting to determine if we have the correct number of check digits.

    For Python and PowerShell, I used split functions. The PowerShell one I did in two routines, one to break the numbers off into $counters, after everything is split.

    $values = $line.Split(' ')
    $counters = $values.split('-')

    From here, I could then break everything up into the 4 parts. 

    $min = $counters[0]
    $max = $counters[1]
    $checkvalue = $values[1].Substring(0, $values[1].Length - 1)
    $pwd = $values[2]

    Now I get a count, and then an IF statement that can clean this up.

    if (($count -ge $min) -and ($count -le $max)) { $part1 += 1}
    if (($checkvalue -eq $pwd[$min-1]) -ne ($checkvalue -eq $pwd[$max-1])) { $part2 += 1}

    This worked right away.

    Part 2

    In Part 2, rather than counting, we are deciding if the check digit is in the positions specified by the numbers.  The trick here is to check if that substring is equal to the digit. Again, in PoSh, the trick is to see if we have one or the other, which is the requirement.

    if (($checkvalue -eq $pwd[$min-1]) -ne ($checkvalue -eq $pwd[$max-1])) { $part2 += 1}

    This code looks if the digit matches first, then the second digit matches, and if these are both a 0 or 1, it’s not valid. If only one is valid, we increment the counter.

    The python code is very similar.

  • 2020 Advent of Code–Day 1

    This series looks at the Advent of Code challenges.

    I started the Advent of Code at the beginning of December 2020, but life quickly got in the way. Weekends especially, where I try to get away from the computer, so I fell behind. However, I did work through a few, and one of my goals in 2021 is to get through all of them.

    I’m going to document my solutions on my blog.

    Day 1

    The first thing I did was set up a template for the solutions. This is clearly important, and I used some basic ASCII art.

    2021-01-22 12_23_37-Day1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (53)) - Microsoft SQL Server Manag

    From here, I tackled the challenge. This is one suited for databases, as there is the need to take a list of numbers and find two that add up to 2020. I created a simple table that contained a single column to store numbers.

    CREATE TABLE Day1
(  datavalue INT)
GO

    In here I inserted the test data from the challenge.

    The easy way for me to tackle this quickly was cross join the numbers with a sum. I put this in a CTE, which gives me the sum of all individual numbers.

    WITH cteCalc (a, b, sumab)
AS (   SELECT
                       a.datavalue, b.datavalue, a.datavalue + b.datavalue AS sumoftwo
        FROM
                       Day1 a
            CROSS JOIN day1 b)

    Once I had this, in the outer query I added a WHERE that limited the results to the sum being equal to 2020, and for the column list, I produced the product.

    WITH cteCalc (a, b, sumab)
AS (   SELECT
                       a.datavalue, b.datavalue, a.datavalue + b.datavalue AS sumoftwo
        FROM
                       Day1 a
            CROSS JOIN day1 b)
SELECT a, b, a * b AS solution
FROM cteCalc WHERE sumab = 2020;
go

    This gave me the result.

    As a hint, I used BULK INSERT to load the complete data from the test file into my table.

    Part 2

    Each challenge has two parts, with the same data. In this one, I had to find 3 entries that summed to 2020. I just added another cross join and this was solved.

    WITH cteCalc (a, b, c, sumabc)
AS (   SELECT
                       a.datavalue, b.datavalue, c.datavalue,
                       a.datavalue + b.datavalue + c.datavalue AS sumoftthree
        FROM
                       Day1 a
            CROSS JOIN day1 b
            CROSS JOIN day1 c
    )
SELECT a, b, c, a * b * c AS solution
FROM cteCalc WHERE sumabc = 2020;
GO

    All in all, an easy day. Now I need to solve this in Python.