Category: Blog

Finding the Titles in R
PASS has released the videos to members from this past Summit. I say TJay Belt today ask about relating a video name to a session. I have the USB drive, so I looked on there. Here are the videos:

Not terribly helpful. If you run the HTML file from the stick, you see this:

If I hover over a title, I see the link as a specific video file. For example, the first one is 65545.mp4. With that, I looked around and found a javascript file with information in it.

The structure was like this:
```
//SID
Col0[0] = "65073";
Col0[1] = "65091";

…

//Speaker Name
Col2[0] = "Steve Stedman";
Col2[1] = "Kellyn Pot'Vin-Gorman";

…

//Session Name
Col4[0] = "Your Backup and Recovery Strategy";
Col4[1] = "DevOps Tool Combinations for Winning Agility";
```
All the data is in one file, but the index in each array matches. So Col0[0] is the SID for video 65073, which has Col2[0] as the speaker and col4[0] as the title.

Now I want to get these in some sort of order. First, let me copy this data into separate files. That will make importing easier. I’ll copy the SID array into one file, the speaker array into a second file and the title array into a third.

This gives me data like the list above, but I need to clean that. This is easiest in Sublime, with a few replacements. I did
- “COL[“ –> “”
- “] = “ –> “,”
- “;” –> “”
This gives me a clean file that looks like this:

Working in R

I almost started to move this into T-SQL and a table, but since I’ve been playing with R, I decided to see what I could do there. First, I know I need to load data, so I the first file into a data frame.
```
session.index = read.csv("e:\\Documents\\R\\videosid.txt", sep=",")
```
The column names aren’t great, so we’ll fix those:
```
 colnames(session.index) <- c("Index", "SessionSID")
```
Now
let’s get the other data.
```
session.speaker = read.csv("e:\\Documents\\R\\passspeaker.txt", sep=",")
> session.title = read.csv("e:\\Documents\\R\\titles.txt", sep=",") 
> colnames(session.speaker) <- c("Index", "Speaker")
> colnames(session.title) <- c("Index", "Title")
```
I have three data frames. I want to combine them. Let’s do that. I’ll use the merge() function to do this. Since I’ve got common column names, I’ll use those.
```
> pass.videos <- merge(session.index, session.title, by="Index")

> pass.videos <- merge(pass.videos, session.speaker, by="Index")
```
This gives me a data frame with the index, title, and speaker. Now I’ve got the data merged, let’s produce a file..
```
 write.table(pass.videos, file="e:\\Documents\\R\\passvideos.txt",sep=",")
```
With that done, I can see I have a list of video numbers, titles, and speakers.
```
"Index","SessionSID","Session","Speaker"
 "1",1,65091,"DevOps Tool Combinations for Winning Agility","Kellyn Pot'Vin-Gorman"
 "2",2,65092,"Oracle vs. SQL Server - The War of the Indices","Kellyn Pot'Vin-Gorman"
 "3",3,65112,"Make Power BI Your Own with the Power BI APIs","Steve Wake"
```
I did something in R.
January 16, 2018
Reading GDPR

In case you’re interested, the GDPR law is actually not bad to read. You might be affected by this, so go through the regulations. I’m doing that this week.

You can also see a nice article from David Poole at SQLServerCentral.

I do think GDPR will affect many of us, but to what extent, I’m not sure. Comments on what you think welcome.

January 15, 2018

tsqlt Tests for Advent of Code 2017 Day 2

This is day 2 of the Advent of Code 2017. If you want to read about the puzzles, start with Day 1. As I worked through the puzzles, I decided that I should be testing using their test sets and solving the issues that way. This lets me use the sample data, but also add in my own sets to cover strange situations.

Here are the tests that I used for each part of day 2.

Part I

This wasn’t a tough puzzle, and the test is fairly simple. I had a function that solves the puzzle with the help of input. My test just sets up the sample input in the table, tab delimited, and then calls the function to calculate the total.

EXEC tsqlt.NewTestClass @ClassName = N'tDay2'
go
CREATE OR ALTER PROCEDURE tDay2.[test day2 sample input]
AS
BEGIN
     ---------------
     -- Assemble
     ---------------
     DECLARE
         @expected INT  18,
         @actual   int;
     
     EXEC tsqlt.faketable @TableName = 'Day2', @SchemaName = 'dbo';
     INSERT dbo.Day2 (DataRow)
      VALUES ('5    1    9    5')
           , ('7    5    3')
           , ('2    4    6    8')

    ---------------
     -- Act
     ---------------
     SELECT  @actual = SUM(b.diff)
      FROM day2 a
      CROSS APPLY dbo.AdventChecksum (a.DataRow) b

    ---------------
     -- Assert    
     ---------------
     EXEC tSQLt.AssertEquals
         @Expected = @expected,
         @Actual = @actual,
         @Message = N'An incorrect calculation occurred.';
END
GO
EXEC tsqlt.run 'tDay2.[test day2 sample input]';

Part II

The test here just calls a different function and has different input.

CREATE OR ALTER PROCEDURE tDay2.[test day2 b sample input]
AS
BEGIN
     ---------------
     -- Assemble
     ---------------
     DECLARE
         @expected INT = 9,
         @actual   int;
     
     EXEC tsqlt.faketable @TableName = 'Day2', @SchemaName = 'dbo';
     INSERT dbo.Day2 (DataRow)
      VALUES ('5    9    2    8')
           , ('9    4    7    3')
           , ('3    8    6    5')

    ---------------
     -- Act
     ---------------
     SELECT  @actual = SUM(b.divmatch)
      FROM day2 a
      CROSS APPLY dbo.AdventChecksum3 (a.DataRow) b

    ---------------
     -- Assert    
     ---------------
     EXEC tSQLt.AssertEquals
         @Expected = @expected,
         @Actual = @actual,
         @Message = N'An incorrect calculation occurred.';
END
GO
EXEC tsqlt.run 'tDay2.[test day2 b sample input]';

GO

January 13, 2018

The Travel Review

I got a note from United this week that summarized my travel for 2017. I’ve been feeling a bit itchy as I haven’t had any trips scheduled for 2018 so far. Strange for me, but looking over the summary, I’m glad.

Last year I flew 44 times with United, for 81k miles. I had 25 domestic flights and 19 international ones. That’s kind of crazy. Of course, outside of Denver, London is my most visited location.

For hotels, I logged 26 stays with Hilton that totaled 62 nights. That was low because I got stuck in a few other hotels at events early in the year. Plus I think I had 8 or 9 Air BnB nights. A lot of time away from home.

I’m a numbers person, so it’s neat to see the summaries from the services I use.

The one big number for me that shows me I traveled too much? Total workouts for the year: 265. That’s way too low.

January 12, 2018

Category: Blog

Finding the Titles in R

Working in R

Reading GDPR

tsqlt Tests for Advent of Code 2017 Day 2

Part I

Part II

The Travel Review