Tag: AI

A friend was asking for help with some data analysis. This was in PowerBI, and the source data was PII. I asked them to mask the data for me to work with but realized that’s a chore. I could mock it myself with AI.

This is part of a series of experiments with AI systems.

The Process

I started by building a table. It’s what I often do to solve problems. I could have asked the AI, but I wasn’t sure what I wanted and across 5 minutes or so I was thinking about the problem and what data was needed.

This is a subset. The original table and model had many more columns, but after thinking for a couple of minutes, I realized these would let me model the report someone wanted. At least enough to see if the requirements might be correct and the code work or not work.

CREATE TABLE MarketData
( UserID INT
, ContactDate DATETIME
, ContactType VARCHAR(10)
, ContactDesc VARCHAR(30)
)
GO

Once I had the code, I jumped over to Claude to give this a try. You can see the prompt below, but it was this: I need sample data for this schema. Duplicate some customerIDs across various dates, and duplicate contacttype for each customer (from 1 to 5), but randomly mix these in across dates. Not every customer needs every contact type. Make this for 10 customers across two years

I could then move on to another task, since I was getting going in the am and answering emails around this task. As it generated, I realized this wasn’t quite what I needed. So I addd this prompt.

I got a bunch of data.

This wasn’t perfect, and I actually went back and asked for more users with only a “1” and got the data altered.

I could have done this, but I’ve also found myself sometimes spending as much time mocking an environment as I did writing a query to solve a problem. I can use this to answer questions on SQL Server Central and even quickly strip code out of poorly asked questions.

It’s amazing. Don’t be afraid of AI. Give it a try. Keep trying. If it fails after a few prompts, just do the work you’re do, but go back and try again later.

I’ve got a video walkthrough of this below.

The more I look to GenAI to save me minutes, short periods of time, the better it works. Here’s an example of something I do regularly where AI helps.

This is part of a series of experiments with AI systems.

Reformatting code

I often see code like this submitted in articles.

The code conversion box often adds extra lines between code. It’s a strange error, and since things get pasted in with lots of formatting, it’s not so simple to fix. I often look at the code side of things and remove lines. This is the raw code:

It’s ugly, and it’s easy to fix, but it takes time and it can be an annoying task. I often might put on a video or multi-task while I handle this simple reformat.

Using Claude

Claude is often my go-to GenAI for now. I tried this prompt, with a CTRL+V of the code. As you can see, Claude starts to reason things out.

On the right side, I can see it rewriting code, which is always fascinating. Reminds me of terminal work over a 300baud modem in university. I’ve grabbed a short snippet of how this works.

Once this is done, I see my code on the right code. I can click “Copy” at the top and then paste back into the editor. It looks much better.

This allows me to move on and tackle some other task before I come back to this, knowing Claude will have helped me.

Small wins, very helpful, but a lever to make me more productive.

I don’t know if this is worth the cost in computing, but it is very helpful. Minutes saved are likely worth a small subscription to the tool every month if I can find a few more tasks like this.

Brent had a query exercise recently about train stations moving in some order and having overrides to this order in emergencies. I suppose he’s been traveling a lot lately and ran into an issue.

In any case, I decided to see if Claude could solve his challenge.

This is part of a series of experiments with AI systems.

The Prompt

I put this into Claude, mostly copy/paste from Brent’s challenge.

I have a sql table of stations in a physical order. Here is the table and data for stations DROP TABLE IF EXISTS dbo.Stations; CREATE TABLE dbo.Stations     (StationId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,      StationName VARCHAR(50),      StationPhysicalOrder INT); INSERT INTO dbo.Stations (StationName, StationPhysicalOrder)     SELECT CHAR(64 + n), n     FROM (VALUES         (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),         (12),(13),(14),(15),(16),(17),(18),(19)     ) AS Numbers(n);

I also have an override table, which contains its own data for emergencies. Here is this code: Transact-SQL 1 2 3 4 5 6 7 8 DROP TABLE IF EXISTS dbo.StationRoutingOverride; CREATE TABLE dbo.StationRoutingOverride     (StationRoutingOverrideId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,      StationFromName VARCHAR(50),      StationToName VARCHAR(50)); INSERT INTO dbo.StationRoutingOverride (StationFromName, StationToName)     VALUES (‘E’, ‘S’), (‘B’, ‘I’), (‘I’, ‘D’);

write a query to return a result set that: * Starts with the station with the lowest StationPhysicalOrder * For each row, if there’s a row in StationRoutingOverride to dictate the next step, jump to that station * Otherwise, go to the station with the next StationPhysicalOrder

The Answer

Here’s the code:

WITH StationRouting AS (
     -- Base case: Start with the station having the lowest StationPhysicalOrder
     SELECT 
         s.StationId,
         s.StationName,
         s.StationPhysicalOrder,
         1 as StepNumber
     FROM dbo.Stations s
     WHERE s.StationPhysicalOrder = (SELECT MIN(StationPhysicalOrder) FROM dbo.Stations)
     
     UNION ALL
     
     -- Recursive case: Find the next station
     SELECT 
         next_station.StationId,
         next_station.StationName,
         next_station.StationPhysicalOrder,
         sr.StepNumber + 1
     FROM StationRouting sr
     CROSS APPLY (
         SELECT TOP 1
             s.StationId,
             s.StationName,
             s.StationPhysicalOrder
         FROM dbo.Stations s
         WHERE s.StationName = COALESCE(
             -- First priority: Check if there's an override for current station
             (SELECT sro.StationToName 
              FROM dbo.StationRoutingOverride sro 
              WHERE sro.StationFromName = sr.StationName),
             -- Second priority: Next station by physical order
             (SELECT TOP 1 s2.StationName 
              FROM dbo.Stations s2 
              WHERE s2.StationPhysicalOrder > sr.StationPhysicalOrder 
              ORDER BY s2.StationPhysicalOrder)
         )
     ) next_station
     WHERE next_station.StationName IS NOT NULL
)
SELECT 
     StepNumber,
     StationId,
     StationName,
     StationPhysicalOrder
FROM StationRouting
ORDER BY StepNumber;

That doesn’t work, so I added to the prompt:

I got a similar error again with the group by in the CTE and pasted that in. The query got rewritten (Again) and this time it ran with this result, which matches Brent’s answer.

GenAIs make mistakes, but they can also fix things. I wondered how well this would work, but it worked well. I merely told the model it had made an error and it tried again.

Imagine if the AI could connect to a db, run its code, and adjust from the error messages itself? The higher end coding tools are supposed to do this.