ConvertTo-SQLSelect

Words: 651

Time to read: ~ 3 minutes

Update 2021-06-17: It now accepts pipeline input

It’s been a busy month for me so there’s not a lot of outside work research that has been going on.

That being said, there has been quite a gap since I wrote a blog post so I figured that I might as well write something

So what do I have to write about?

SELECT Statements

There are times when I want to mess about with data in SQL Server, data that I have obtained in PowerShell. This will require some way to get the information from PowerShell into SQL Server.

I know of a few ways to do this.

dbatools

There is the dbatools module and the Write-DbaDbTableData function.

Get-Help -Name Write-DbaDbTableData -Full

If I wanted to write the properties of 50 modules from PSGallery into SQL Server, I can use the function handy enough.

Find-Module | Select-Object -First 50 | Write-DbaDbTableData -SqlInstance localhost -Database WAT -Table dbatools_Insert -WhatIf

ImportExcel

There is also the ImportExcel module and the ConvertFrom-ExcelToSQLInsert function.

Get-Help -Name ConvertFrom-ExcelToSQLInsert -Full
Find-Module | Select-Object -First 50 | Export-Excel -Path .\Documents\Excel\temp_20210614.xlsx;
ConvertFrom-ExcelToSQLInsert -TableName ImportExcel_Insert -Path .\Documents\Excel\temp_20210614.xlsx -UseMsSqlSyntax

Being Picky

Both of these were a bit too much for me though. I only wanted a quick and easy way to have the data available in a SELECT statement.

I can use ImportExcel and ConvertFrom-ExcelToSQLInsert but that is dependent on the table already existing, never mind having to save the data in an Excel file first.

Don’t get me wrong – I’m aware that you don’t need Excel installed on the computer where you’re running these commands from. You still need to save the files somewhere though. The function doesn’t take data from variables.

I can use dbatools and Write-DbaDbTableData. This function is not dependent on the table having to already exist. It will create the table for you if you tell it to. Thank you -AutoCreateTable; even though I recommend pre-sizing your columns if you want to go with this method.

However, I don’t want to have to create the table beforehand.

ConvertTo-SQLSelect

So I wrote a primitive function to have the data available in a SELECT statement that I can run in an SSMS or Azure Data Studio window.

You can find the code for it here on Github:
ConvertTo-SQLSelect

I can pass a bunch of objects into it and it will create the SELECT for me using the good ol’ VALUES clause.

Although I’m pretty sure this is basically what ORMs do under the cover before people who knew what they were doing looked at them…

ConvertTo-SQLSelect -Data (Find-Module | Select-Object -First 50)
… there’s more data here….

Caveats

There are a couple of caveats to be aware of…

  • It doesn’t allow pipeline input.

It probably could but that would require a sit-down and think about how to do it. Like I said; this was a quick and dirty put-together function.

It now accepts pipeline input – although I’m sure it isn’t the best way I could have implemented that…

-999..1000 | ForEach-Object -Process { (Get-Date).AddDays($_) } | ConvertTo-SQLSelect
  • There are no data types.

There are strings and they get inserted as strings but that’s okay for me for a quick playthrough. Any data conversions, I can do once I have the data in an SSMS window.

  • It doesn’t like single quotes

Yeah, I have no real excuse for this one. I should really fix that before I use this function again…

It can handle single quotes now

  • There is also no help comments for this.

There should be, even though there is only one parameter. There should also be tests! I am filled with good intentions that are yet to see fruition though…

That being said, I’ve had to use it a few times already that has meant that writing it has already paid off.

So feel free to use, abuse, and/or improve it as you see fit.

I hope you find it useful.

Pester 5 and Group-Object – Best Friends

Figuring out how to group the output of your Pester tests

Words: 830

Time to read: ~ 4 minutes

I’ve been working with Pester v5 lately.

Pester v5 with PowerShell v5 at work & Pester v5 with PowerShell Core outside of work.

There are quite a few changes from Pester version 3, so it’s almost like learning a new language… except it’s based on slang. I think that I’m speaking eloquently, and then I’ve suddenly insulted someone and Pester no longer wants to play nice with me.

Initial Tests

I’ve got the following data that I’m using to test Pester v5.

BeforeDiscovery -ScriptBlock {
    $Groups = @(
        [PSCustomObject] @{
            Server = 1
            Group = 'A'
            Value = '86b7b0f9-996f-4c19-ac9a-602b8fe4d6f2' -as [guid]
        }, 
        [PSCustomObject] @{
            Server = 1
            Group = 'B'
            Value = 'e02913f7-7dae-4d33-98c9-d05db033bd08' -as [guid]
        },
        [PSCustomObject] @{
            Server = 2
            Group = 'A'
            Value = '96ad0394-8e9e-4406-b17e-e7d47f29f927' -as [guid]
        },
        [PSCustomObject] @{
            Server = 2
            Group = 'B'
            Value = 'f8efa8b6-e21b-4b9c-ae11-834e79768fee' -as [guid]
        }
    )
}
Image showing the data in the Before Discovery block
Test data

Usually, I would only use -TestCases to iterate through the data. I know that in Pester v3, I could wrap the It blocks inside a foreach () {}, and it would be okay. Hell, in most of my testings, it was faster. It doesn’t matter; I liked using -TestCases, and the performance difference is negligible to me.

That is still an option with Pester v5. I can run the below code to confirm.

Describe -Name 'Attempt: 01' -Tag '01' -Fixture {
    Context -Name 'Server: <_.Server>' -Fixture {
        It -Name 'should have a guid for its value: <_.Value>' -TestCases $Groups {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
ForEach on the It block

If I look at the data, I can see that I’ve got two different values for Server; 1 and 2. It would be great if I could group the tests by those server values.

For me, Pester has three main blocks; Describe, Context, and It.
I know that Pester v5 has a -ForEach parameter for each of these blocks. I’ve already tried using the -ForEach parameter against the It block, and it didn’t do what I wanted.

Reminder of the ForEach on the It block

I’ll try it against the Context block instead and see if it works.


Describe -Name 'Attempt: 02' -Tag '02' -Fixture {
    Context -Name 'Server: <_.Server>' -Foreach $Groups {
        It -Name 'should have a guid for its value: <_.Value>' -Test {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
ForEach on the Context block

That kind of works but we’ve got the same server in two different groups. Let’s move the groups up to the Describe level.

Describe -Name 'Attempt: 03 - Server: <_.Server>' -Tag '03' -Foreach $Groups {
    Context -Name 'Server: <_.Server>' -Fixture {
        It -Name 'should have a guid for its value: <_.Value>' -Test {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
ForEach on the Describe block

We’ll that’s not what I wanted. Instead of 1 describe block, we have multiple blocks; 1 per group.

Grouped Data

Now, I’m going to start using Group-Object. My data by itself doesn’t seem to work.

$Groups = @(
    [PSCustomObject] @{
        Server = 1
        Group = 'A'
        Value = '86b7b0f9-996f-4c19-ac9a-602b8fe4d6f2' -as [guid]
    }, 
    [PSCustomObject] @{
        Server = 1
        Group = 'B'
        Value = 'e02913f7-7dae-4d33-98c9-d05db033bd08' -as [guid]
    },
    [PSCustomObject] @{
        Server = 2
        Group = 'A'
        Value = '96ad0394-8e9e-4406-b17e-e7d47f29f927' -as [guid]
    },
    [PSCustomObject] @{
        Server = 2
        Group = 'B'
        Value = 'f8efa8b6-e21b-4b9c-ae11-834e79768fee' -as [guid]
    }
)
Results of the Test Data

We can pass that data into Group-Object to group our data by a certain property. In my case, I want to group the data by the Server property.

$Groups | Group-Object -Property Server
Grouped Test Data

Taking a look at the first group, I only have the data for that single property value.

($Groups | Group-Object -Property Server)[0].Group
Inside the first group of Test Data

Now, I’ll try the Pester code again.

Grouped Tests

First, I’ll try putting the groups into the It blocks and see if that works.

Describe -Name 'Attempt: 05' -Tag '05' -Fixture {
    BeforeDiscovery -ScriptBlock {
        $GroupedGroups = $Groups | Group-Object -Property Server
    }

    Context -Name 'Server: <_.Name>' -Fixture {
        It -Name 'should have a guid for its value: <_.Group.Value>' -ForEach $GroupedGroups {
            $_.Group.Value | Should -BeOfType [guid]
        }
    }
}
Grouped on the It block

It doesn’t fully work. The data is grouped but the results seems to be concatenating the values. I’d like it better if they were split out to separate tests per value.

This time, I’ll group the data in the context blocks and then pass the groups into the It blocks. I’ll do this by passing the groups into the -ForEach parameter of the It block using $_.Group.

Describe -Name 'Attempt: 04' -Tag '04' -Fixture {
    BeforeDiscovery -ScriptBlock {
        $GroupedGroups = $Groups | Group-Object -Property Server
    }

    Context -Name 'Server: <_.Name>' -ForEach $GroupedGroups {
        It -Name 'should have a guid for its value: <_.Value>' -TestCases $_.Group {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
Grouped on Context and passed to It block

In the previous code blocks, I used the BeforeDiscovery block in the Describe block. If you don’t want to use that, you can pass the Group-Object cmdlet to the ForEach parameter as a subexpression.

Describe -Name 'Attempt: 06 - Server: <_.Name>' -Tag '06' -ForEach ($Groups | Group-Object -Property Server) {
    Context -Name 'Server: <_.Name>' -Fixture {
        It -Name 'should have a guid for its value: <_.Value>' -TestCases $_.Group {
            $_.Value | Should -BeOfType [guid]
        }
    }
}
Without using BeforeDiscovery on the Describe block

Pass or Fail

I’ve encountered this obstacle of grouping objects in tests a couple of times. I’m hoping that by writing this down, I’ll be able to commit the information to memory.

Hey, if it doesn’t, I can always grab the code and figure it out.

T-SQL Tuesday #135: The outstanding tools of the trade that make your job awesome

Welcome to T-SQL Tuesday, the brainchild of Adam Machanic ( twitter ) and ward of Steve Jones ( blog | twitter ).
T-SQL Tuesday is a monthly blogging party where a topic gets assigned and all wishing to enter write about the subject.
This month we have Mikey Bronowski ( blog | twitter ) asking us about the most helpful and useful tools we know of or use.

Tools of the trade are a topic that I enjoy. I have a (sadly unmaintained) list of scripts from various community members on my blog. This list is not what I’m going to talk about though. I’m going to talk about what to do with or any scripts.

I want to talk about you as a person and as a community member. Why? Because you are the master of your craft and a master of their craft takes care of their tools.

Store Them

If you are using scripts, community-made or self-made, then you should store them properly. By properly, I’m talking source control. Have your tools in a centralised place where those who need it can access it. Have your scripts in a centralised place where everyone gets the same changes applied to them, where you can roll back unwanted changes.

Check out Brett Miller’s ( blog | twitter ) presentation “GitOps – Git for Ops people“.

Version Them

If you are using community scripts, then more likely than not, they are versioned. That way you’re able to see when you need to update to the newest version. No matter what language you’re using, you can add a version to them.

PowerShell has a ModuleVersion number, Python has __version__, and SQL has extended properties.

Or even take a page out of Bret Wagner’s ( blog | twitter ) book and try XML comments.

Take Care of Them

If you take care of these tools, if you store them, version them, and make them accessible to those who need them, then they will pay you back a hundredfold.
You’ll no longer need to re-write the wheel or pay the time penalty for composing them. The tools will be easy to share and self-documented for any new hires.
Like the adage says: Take care of your tools and your tools will take care of you.

Start-IncreasingPushback

Words: 498

Time to read: ~3 minutes

Intro

If you look back over some of the posts that I wrote in October this year, you may have realised that there was a motif going on.

I used a homebrew pushup tracker as a data source for a couple of blog posts. A group of friends and I were attempting to “push out” (excuse the pun) 3,000 pushups over the month.

Spoilers: We didn’t reach the target. 

Try Again

I’m okay with failure. If you learn from your failures, then I don’t even consider them as failures. This scenario didn’t fall into this case, though. The only reasons that I could think that I didn’t reach the target are:

  1. I started after nearly a week into the month had passed, and
  2. I tried to do too much, too fast, in as little rounds as possible per day.

So, with these lessons under my belt, I decided to try again.

Smarter

I figured that it was simple enough to fix my first mistake, I’d start on the first day of the month this time.

The second mistake was something that I figured would also be simple. Rather than attempting to do as many as I could in as little rounds as possible, I’d do ten sets a day and that was it. If I focus more on the process than the goal, I figured that it would get me over the line eventually.

Challenge 01

If I do a set every half hour, I’d have the ten completed in 5 hours. I mean, hey, we’re in lockdown. I have 5 hours to spare.

But I didn’t.

Work, meetings, calls, focus and flow all sapped the time away from me.

So I tried again.

I’ve started getting up early in the mornings do to research and blog posts (like this one for example), so I’d try and get them done then.

Ten sets every 5 minutes should have me completed in just under an hour; more than enough time to spare.

Challenge 02

Pushups are hard! Even when I’m not trying to rep out as many as I can, they still take a toll on the body. Soon a five-minute break is not enough, and I’m taking longer and longer rests.

Fine, if that’s the way we’re going to do this, then I’m going to go with the flow.

Scripting

Seeing as I needed a little extra rest each round, I decided to create a PowerShell script that would help calculate that rest for me.

https://github.com/shaneis/RandomScripts/blob/master/Start-IncreasingBackoff.ps1

Using the Script

For once, I’ve added comment based help to my script so I can run

Get-Help -Name Start-IncreasingBackup -Examples

and get examples of what the script does!

Now, I can run this script and get a timer that will let me know when to start my activities and that will give me more and more rest each time!

Now to see what else I can use this for!

Uncommon SQL

Words: 612

Time to read: ~ 3 minutes

Intro

Recently the DBA Team Lead and I were reviewing some SQL code, and we came across some SQL that neither of us had frequently encountered before. This led to a brief watercooler moment where we shared some uncommon SQL that we had seen. Perfect blog post material, I think.

ODBC Date Functions

From a previous post, I talked about ODBC date functions.

I’m using AdventureWorks2014 here.

/* The 10 employees who have been the longest at the company */
SET NOCOUNT ON;

SELECT TOP (10) WITH TIES
        HE.JobTitle,
        HE.HireDate,
        {d '2006-06-30'} AS start_of_company,
        DATEDIFF(DAY, {d '2006-06-30'}, HE.HireDate) AS days_since_company_start
FROM    HumanResources.Employee AS HE
ORDER BY    days_since_company_start;

INSERT Alias

An unexpected item that we found recently was that INSERT INTO statements care about correct column names. That’s all though, nothing else seems to faze them.
This means that you can add the most ridiculous aliases or part names to the column and SQL Server won’t care. As far as I can tell, it will just ignore them.

/* Prefixes get prefixed */
SET NOCOUNT ON;

IF OBJECT_ID(N'dbo.Hires', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Hires;
END;

CREATE TABLE dbo.Hires (
    hire_id int IDENTITY(1, 1) NOT NULL
        CONSTRAINT [PK dbo.Hires hire_id] PRIMARY KEY CLUSTERED,
    job_title varchar(50) NOT NULL,
    hire_date datetime2(7) NOT NULL,
    is_on_salary bit NULL
        CONSTRAINT [DF dbo.Hires is_on_salary] DEFAULT (0)
);

TRUNCATE TABLE dbo.Hires;

WITH OldestHires AS (
SELECT TOP (10) WITH TIES
        HE.JobTitle AS job_title,
        HE.HireDate AS hire_date,
        ROW_NUMBER() OVER (ORDER BY HE.HireDate) AS rn
FROM    HumanResources.Employee AS HE
ORDER BY    HE.HireDate
)
INSERT INTO dbo.Hires (
    [0].[1].[2].[3].[4].[5].[6].[7].[8].[9].job_title,
    a.b.c.d.e.f.g.h.i.j.k.l.m.n.o.p.q.r.s.t.u.v.w.x.y.z.hire_date,
    [1/0].[%].[OUT_OF_BOUNDS].[   ].is_on_salary
)
SELECT  OH.job_title,
        OH.hire_date,
        CASE 
            WHEN OH.rn % 3 = 0 THEN NULL
            ELSE 1
        END AS is_on_salary
FROM    OldestHires AS OH;

SELECT  *
FROM    dbo.Hires;
GO

Default Option

Let’s contrive an example. Let us say that we have a table called dbo.Hires and we’ve added a column called is_on_salary.
Since most of the hires are salaried, we have added a new default constraint setting the value to 0.
Unfortunately, it looks like the default constraint hasn’t been applied yet…

/* Our dbo.Hires table */
SET NOCOUNT ON;

SELECT  *
FROM    dbo.Hires;

= DEFAULT

Recently, my DBA Team Lead pointed me to a piece of code where the syntax was: UPDATE T SET COLUMN = DEFAULT

Now, I had never seen this before, and I wasn’t quite sure that this method would work. I wasn’t wholly surprised, though when a quick test proved that it does.

/* UPDATE DEFAULT */
SET NOCOUNT ON;

UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary IS NULL;

SELECT  *
FROM    dbo.Hires;

What about with no default?

Okay, that seems to add the default constraint to a column. What about when there is no defined constraint on the column. Will it error out then?

/* Removing our default constraint */
ALTER TABLE dbo.Hires
    DROP CONSTRAINT [DF dbo.Hires is_on_salary]

SELECT  'Pre update' AS [status],
        *
FROM    dbo.Hires;

UPDATE  dbo.Hires
SET     is_on_salary = DEFAULT
WHERE   is_on_salary = 0;

SELECT  'Post update' AS [status],
        *
FROM    dbo.Hires;

Nope! As mentioned in the docs – if there is no default, and the column can become NULL, then NULL will be inserted.

CURRENT

Finally, we have CURRENT.
While the vast majority of scripts manually define the database context for commands, such as ALTER DATABASE AdventureWorks, etc., you can tell SQL Server:
Hey! Use the current database context!

/* CURRENT Database Context */
SET NOCOUNT ON;

ALTER DATABASE AdventureWorks2014 SET PAGE_VERIFY NONE;

SELECT 'Pre change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';

ALTER DATABASE CURRENT SET PAGE_VERIFY CHECKSUM;

SELECT 'Post change' AS [status], [name], page_verify_option_desc FROM [sys].[databases] WHERE [name] = N'AdventureWorks2014';

And so forth

Thre’s probably a lot more but these are the ones that we talked about. If you have any uncommon SQL, let me know!

PIVOT in PowerShell

Words: 1151

Time to read: ~ 6 minutes

Apologies

I’m going to start this post off with an apology.

As Kevin Feasel ( Blog | Twitter ) mentioned about my last post Attempting SUM() OVER () in PowerShell:

It’d be a lot easier, though, with a properly normalized data model which includes date, attempt number, and push-ups in that attempt. Pivot those results at the end if you want this sort of report, but SQL is designed to work best with tables in first normal form or higher.

Kevin Feasel

I can’t very well give out to people for not doing the right thing first time, even if it’s more difficult, if I don’t do the right thing myself!

As Kevin mentioned, once the data was in a proper format, a format designed for SQL, the calculations were trivial.

However, outputting the results in the same way in PowerShell required a way to pivot results in PowerShell. Thanks to some heavy lifting from Joel Sallow ( Blog | Twitter ), I now know how to pivot in PowerShell!

Here’s hoping that this post will help explain it for you also.

Exploring our Data

SQL

First off, let’s check the current state of our table in SQL.

SELECT	POP.pushup_date,
		POP.attempt_number,
		POP.pushup_count,
		SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total_per_date,
		SUM(POP.pushup_count) OVER () AS grand_total
FROM	dbo.PushupsOctoberProper AS POP;
SQL style!

Pivoting

I want to get all possible 8 attempts horizontal like the last post. I find this fairly easy when I have the documentation for PIVOTs open in another tab.

/* Can we pivot these? */
SELECT	PVT_01.pushup_date,
		[1] AS attempt_1,
		[2] AS attempt_2,
		[3] AS attempt_3,
		[4] AS attempt_4,
		[5] AS attempt_5,
		[6] AS attempt_6,
		[7] AS attempt_7,
		[8] AS attempt_8,
		PVT_01.total,
		PVT_01.total_so_far
FROM
(
	SELECT	POP.pushup_date,
			POP.attempt_number,
			POP.pushup_count,
			SUM(POP.pushup_count) OVER (PARTITION BY POP.pushup_date ORDER BY POP.pushup_date) AS total,
			SUM(POP.pushup_count) OVER () AS total_so_far
	FROM	dbo.PushupsOctoberProper AS POP
) AS SRC
PIVOT
(
	MAX(pushup_count) FOR attempt_number IN ([1], [2], [3], [4], [5], [6], [7], [8])
) AS PVT_01
ORDER BY	PVT_01.pushup_date;
Simples!

Simple, right? Once we have the data in the expected format then the above steps are the only steps necessary to calculate and show the data in the way that we want.

However, it becomes a bit more complicated in PowerShell.

PowerShell

Let’s grab the data from our SQL instance and take a look at it.

<# Populate our variable from the database #>
$invQueryParams = @{
    SqlInstance = $sqlInstance
    Database = 'LocalTesting'
    Query = 'SELECT * FROM dbo.PushupsOctoberProper;'
}
$data = Invoke-DbaQuery @invQueryParams

<# Show our data #>
$data | Format-Table -Autosize
So far, so good…

Grouping our Data

We have our data fetched, now we need to group it by the different dates. If only PowerShell had a way to group objects…what? Group-Object? oh!

<# Grouping our data #>
$dataGroups = $data | Group-Object -Property pushup_date
$dataGroups
Data.DataRow? * sigh* one of these days I’ll remember to use -AS PSObject with my Invoke-DbaQuery

Now that we have our data grouped by the different dates, we can loop through each date and pivot the data out horizontally.

Manual Pivot

The first way that came to mind was to manually list out all columns. I know that the maximum attempt_count that I have is 8 so let’s manually create 8 attempt columns.

<# Let's pivot this manually because it's the first way that came to mind #>
$ManualpivotedData = foreach ($dg in $dataGroups) {

    [PSCustomObject]@{
        pushup_date = ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString()
        attempt01 = ($dg.Group | Where-Object attempt_number -eq 1).pushup_count
        attempt02 = ($dg.Group | Where-Object attempt_number -eq 2).pushup_count
        attempt03   = ($dg.Group | Where-Object attempt_number -eq 3).pushup_count
        attempt04   = ($dg.Group | Where-Object attempt_number -eq 4).pushup_count
        attempt05   = ($dg.Group | Where-Object attempt_number -eq 5).pushup_count
        attempt06   = ($dg.Group | Where-Object attempt_number -eq 6).pushup_count
        attempt07   = ($dg.Group | Where-Object attempt_number -eq 7).pushup_count
        attempt08   = ($dg.Group | Where-Object attempt_number -eq 8).pushup_count
        total = ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum
        total_so_far = ($data | Measure-Object -Property pushup_count -Sum).Sum
    }
}

<# Let's make this pretty #>
$ManualpivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
Seems to work

In case you’re wondering what @{ Expression = 'attempt*' ; Width = 10 } does, I use it to narrow the width of the columns named like attempt since they’re integers. Since they don’t need as much space, I can narrow them down and then Format-Table won’t cut-off my later columns!

Dynamic Pivot

I’m not against the manual way. I just find it too bulky and repetitve. It works! Please don’t get me wrong on that accout but as I recently heard someone say: “It works, now clean it up

Our main problem is the attempt columns and our manually typing them out. They seem like a perfect candidate for a ForEach loop. But, when we try to slot that in….

foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
            @{ 
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count } 
            }
        }
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
    )

    $dg | Select-Object $props
}
Well that shouldn’t be empty!

Yeah, that’s a “no” from PowerShell. Why is this?

Why this is

Let’s investigate that $props variable. We’re creating a hashtable where the Key is our name and the Value is the expression we want. So let’s get the values.

$props | Format-List
Expression = $num

Do you see the way that each of the Expression keys have a value with the $num variable?

If you check $num now, you’ll see that it’s set to 8. It looks like we have found our problem, the $props variable isn’t keeping the value of $num when we define it!

Since only one date has a value for attempt 8, we should see some values there.

$pivotedData = foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
            @{ 
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count } 
            }
        }
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
    )

    $dg | Select-Object $props
}

<# Let's check the 14th #>
$pivotedData | Where-Object pushup_date -eq '14/10/2020'
All filled but all with value for the 8th attempt!

Yeah…that’s not correct. I did 30 on the first attempt. Believe me, I remember the pain. Looks like it’s putting the value for attempt 8 into each of the attempts.

Not cool…

Closures

If only there was a way to keep the value of $num when we defined the $props variable. Well, thanks to Joel and his post ScriptBlocks and GetNewClosure(), I now know that there is!

$pivotedData = foreach ($dg in $dataGroups) {

    $props = @(
        @{ Name = 'pushup_date' ; Expression = { ($dg.Group | Select-Object -ExpandProperty pushup_date -Unique).ToShortDateString() }}
        foreach ($num in 1..8) {
            @{ 
                Name = "attempt_$num" 
                Expression = { $dg.Group |
                    Where-Object attempt_number -eq $num |
                    Select-Object -ExpandProperty pushup_count }.GetNewClosure()
            }
        }
        @{ Name = 'total' ; Expression = { ($dg.Group | Measure-Object -Property pushup_count -Sum).Sum } }
        @{ Name = 'total_so_far' ; Expression = { ($data | Measure-Object -Property pushup_count -Sum).Sum }}
    )

    $dg | Select-Object $props
}

<# Let's make this pretty #>
$pivotedData | Format-Table -Property pushup_date, @{ Expression = 'attempt*'; Width = 10 }, total, total_so_far
It’s alive!!!!!

Summary

There’s nothing wrong with making mistakes; as long as you learn from them.

Thanks to Kevin for reminding me how things should be stored, and thanks to Joel for this (vast) knowledge sharing, I’ve been able to learn how to dynamically pivot in PowerShell from my mistakes.

Review your mistakes, you never know what you may learn.

Attempting SUM() OVER () in PowerShell

Words: 891

Time to read: ~ 5 minutes

Pro-Cras-Tin-Ation!

Like most things in life, this piece of work came about while attempting to complete something else. It’s not a bad thing, I expect it at this stage.

Easy Like Sunday Morning

I find it easy to get the total of a row in SQL. Hell, when it is not particularly important, I’ll even go the easy route and use a calculated column in the table.

CREATE TABLE dbo.PushupsOctober
(
	pushup_date date NOT NULL
		CONSTRAINT PK_PushupsOctober PRIMARY KEY CLUSTERED,
	attempt_01 tinyint NULL,
	attempt_02 tinyint NULL,
	attempt_03 tinyint NULL,
	attempt_04 tinyint NULL,
	attempt_05 tinyint NULL,
	attempt_06 tinyint NULL,
	attempt_07 tinyint NULL,
	attempt_08 tinyint NULL,
	total_pushups_per_day AS (ISNULL(attempt_01, 0) + ISNULL(attempt_02, 0) + ISNULL(attempt_03, 0) + ISNULL(attempt_04, 0) + ISNULL(attempt_05, 0) + ISNULL(attempt_06, 0) + ISNULL(attempt_07, 0) + ISNULL(attempt_08, 0))
);
GO

Then, all I have to do is insert the data and SQL will automatically take care of calculating the total per row for me.

INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/07/2020 00:00:00', 20, 20, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/08/2020 00:00:00', 20, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/09/2020 00:00:00', 20, 20, 25, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/10/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/11/2020 00:00:00', 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/12/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/13/2020 00:00:00', 20, 15, 15, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/14/2020 00:00:00', 30, 30, 20, 20, 25, 20, 20, 20);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/15/2020 00:00:00', 25, 25, 25, 25, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/16/2020 00:00:00', 25, 25, NULL, NULL, NULL, NULL, NULL, NULL);
INSERT INTO dbo.PushupsOctober (pushup_date, attempt_01, attempt_02, attempt_03, attempt_04, attempt_05, attempt_06, attempt_07, attempt_08) Values('10/17/2020 00:00:00', 25, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
GO

SELECT	*
FROM	dbo.PushupsOctober;
GO
Why count when not need to?

Once you have the total per row, you throw in a SUM(that total) OVER () and you have a grand total. Thank you to Kevin Wilkie ( blog | twitter ) for re-igniting my curiosity about Window Functions again.

SELECT	*,
		SUM(p.total_pushups_per_day) OVER () AS total_so_far 
FROM	dbo.PushupsOctober AS p;
GO
Total total

Easy Like Monday Morning

PowerShell is a different beast. Please don’t get me wrong; I still love the language. I don’t find it easier to get a row total and then a grand total though.

It’s possible! I’m just hoping that there is a better way. Saying all that here is my attempt at a row total and grand total using PowerShell.

If you have a better way (you choose the conditions that satisfy “better”) please let me know.

Grabbing the Data

First, let’s grab the data from the table in our database.

$data_2 = Invoke-DbaQuery -SqlInstance localhost -Database LocalTesting -Query @'
SELECT * FROM dbo.PushupsOctober;
'@

Removing Unwanted Properties

Here’s where I remembered that I had a calculated column, realised that it would be cheating to use it and decided it needed to go. Thankfully, this also enabled me to get rid of those pesky columns that get returned from Invoke-DbaQuery when you forget the parameter -As PSObject!

$data_2 = $data_2 | Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors, total_pushups_per_day

Grabbing Property Names

There’s a couple of things that we need here. We need a way to add up all the “attempt” columns so we need a way to select them all.

$props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name

There, that should do nicely!

Grabbing the Values for those Properties

Now, we can iterate over them and get all the values.

foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }

Potential Problem

See all those empty lines? Yep, that’s a potential problem for Measure-Object.

$hasToBeAnEasierWay = foreach ($p in $props) { $data_2[0] | Select -ExpandProperty $p }
$hasToBeAnEasierWay | Measure-Object -Sum

Removing NULL or WhiteSpace

Thankfully, there’s a way to get rid of those empty lines.

$hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum

Putting it ALL Together

Now that we have the skeleton of a script ready, let’s put it all together.

Row total

$data_2 | ForEach-Object -Begin {
    $props = $data_2[0].PSObject.Properties | Where-Object Name -like 'attempt*' | Select-Object -ExpandProperty Name
} -Process {
    $total = $null
    $hasToBeAnEasierWay = $null

    $hasToBeAnEasierWay = foreach ($prop in $props) {
        $_ | Select-Object -ExpandProperty $prop
    }
    $total = ($hasToBeAnEasierWay | Where-Object { -Not [String]::IsNullOrWhiteSpace($_) } | Measure-Object -Sum).Sum

    $_ | Select-Object -Property *, @{
        Name = 'total_per_day'
        Expression = { $total }
    }, @{
        Name = 'days_left'
        Expression = { ((Get-Date -Date '2020-10-31') - (Get-Date -Date $_.pushup_date)).Days }
    }
} -OutVariable data_3 | Format-Table -Autosize

We now have the row total in our total_per_day property. And, with our use of -outvariable data_3, we have the results saved into a variable called $data_3 .

Grand Total

Once we have a single column that we can sum up to give us our grand total, then PowerShell makes this operation trivial.

I do have to use Format-List here because Format-Table can’t fit all the properties in so our new property total_so_far won’t show up.

$data_3 | Select-Object -Property *, @{
    Name = 'total_so_far'
    Expression = { ($data_3 | Measure-Object -Property total_per_day -Sum).Sum }
} | Format-List

There We Go!

While, I’d argue that it’s not as easy as SQL, it’s completely possible to get row totals and grant totals in PowerShell.

Honestly though, I hope there’s an easier way. Otherwise, I’m going to do it in SQL and then grab it out into PowerShell afterwards.

T-SQL Tuesday #131: Data Analogies, or: Explain Databases Like I’m Five!

Words: 906

Time to read: ~ 5 minutes

Welcome to T-SQL Tuesday! This month’s host is Rob Volk (blog|twitter) and the topic is Data Analogies.

Analogies

Come in! Come in, my boy!
Now, your father sent you to me to explain what I used to do.
Well, I was a DBA, my boy!
What do you mean “what does that do”? I took care of the databases! I made sure that they were looked after, that they had vim and vigour, and that they didn’t go down.

What? No, I don’t mean “down-down”, it’s not a direction! I mean that they didn’t go offline. No, not “off-off”, well not quite… It was my duty to ensure that they were available. Got it? No?

Database Administration

Well, let’s take that whiskey cabinet over there Jas…Jaeysin. Let us say that the whiskey cabinet is our database, and it is our job to see that it is never empty. We do that by lots of ways; by checking on it and making sure that nothing is alarming, like empty bottles. We check that there is space if someone wants to add more whiskey and that anyone who wants something from it, can get it. Like me, hehe.

What? You don’t understand how that’s like being a DBA? Well think about it my boy, I would check on the databases, make sure nothing was alarming, and that the data was always available for whoever wanted it.

Security

What’s that? You want some? Ho ho, my boy, you are one for jests. I tell you what, try opening the cupboard door on the left. Yes, the one where you can see all the sticks and cherries through the glass. Not a problem for you, was it? Put back the cherry, please. And wipe your hands…NOT ON THE GLASS!
Nevermind, my boy, nevermind, I shouldn’t have put the soda water in a dangerous place like that…inside a cupboard…away from the ledge. Try and open the right cupboard door now. Yes, the one with the fancy bottles and the shiny lights. Yes, I’m aware it’s locked. Now see, you cannot open that door but I can because I have permission to open that door.
That was another part of my job, making sure that people had the right permission to get what they wanted, and that people without permission could not.

What’s that? Who decides on the permissions? Well, back then, it was a business decision. Where those higher up in standing would pick, and I would have to follow their instructions. Now, I’m in charge.

What do you mean that’s not what your father says? Right, well, I’ll be having a few words with him, and we’ll see more about this “under his partner’s thumb” business. No, I can’t open it either. Because I don’t have the key. Yeah well, I may be db_owner, but not sysadmin… Nevermind.

Performance

What else did I do? Well, I made sure those who wanted data from the database knew where they could get the data and could get the data promptly.
Well, do you see the whiskey cabinet? Yes, the one on the right. Yes, the one you’re not allowed open. Yes, you’re allowed to look…oi! Quit your cheek, or I’ll add that to the list of things I’ll have to talk to your father about.
Now, if someone were to go to that cabinet wanting a nice Scotch, they only have to reach into the middle shelf, and they would have a whole choice of Scotch to choose from. I know that because I know that my middle shelf is only for Scotch.
Same with the databases; I helped people to create tables to hold their similar data the same way I choose that shelf to have my Scotch.

And see the way that the bottles get fancier as you scan your eyes from left to right. I ordered that shelf, so the most expensive bottles were all the way over there on the right. And, the least expensive bottles are all over to the left.
Same with the databases; I would create indexes so that people could go to what they wanted, whether that be the “expensive bottles” or not.
No more looking through the entire table for what they want, they knew exactly where to go and knew when they could stop looking as well.

What? Indexes, my boy, an index. No, it hasn’t nothing to do with your finger! Wait! You may be on to something there. Tell me, have you ever run your index finger down a phone book? A phone book. You’ve never heard of it? Hold on…this thing? Ever seen this? Big yellow book, lots of information? Yes, I know we have Google, nevermind.

Redundancy

Was that it? No lad, that was not it. The world of data is vast and open. So much more than what an analogy using a whiskey cabinet can provide. But I will leave you with one more analogy; Redundancy. It’s always essential to have a redundant copy of your data, a recovery plan if a disaster were ever to strike.
Open that desk drawer for me, my boy. Yes, it’s another bottle of whiskey. A redundant backup, if you would. Now I believe I see your father pull into the drive, so it is time for you to leave. I shall go see if my backup can still be restored.

Goodbye, you little disaster.

ODBC date/time Extension Option in SQL Server

Words: 329

Time to read: ~ 2 minutes

At this stage of my career with Transact-SQL, I would like to think that I have the language mostly mapped. If there is something that seems new or unusual, then it often comes down to the developers marking the code to make replacing it more effortless.

In case you don’t grasp what I’m on about, the below piece of code shows it pretty well.

SELECT N'##datetime##' AS search_and_replace;

<clickbait>I had never seen this before</clickbait>

Can you imagine my shock when I came across a piece of code that not only was not for finding and replacing but even though I did not think it would compile, it did!

If you can imagine my shock, then you’re going to need to increase it more when I tell you that there are a whole family of the same functions!
Here is the code that threw me for a loop the first time I saw it.

SELECT {d '1970-01-01'};

Researching

There are more than just {d '<datetime>'} as well!

SELECT {d '2020-01-01'} AS sample_date,
       {t '12:31:56'} AS sample_time,
       {ts '2020-01-01 12:31:56'} AS sample_datetime;

There’s a whole page in Microsoft documentation about them.

It’s not just date and time functions but string function as well.

SELECT LEN(N'This string is 33 characters long') AS string_length,
       {fn BIT_LENGTH(N'This string is 33 characters long')} AS string_bit_length,
       {fn OCTET_LENGTH(N'This string is 33 characters long')} AS string_nvarchar_octet_length,
       {fn OCTET_LENGTH('This string is 33 characters long')} AS string_varchar_octet_length,
       DATALENGTH(N'This string is 33 characters long') AS string_nvarchar_datalength,
       DATALENGTH('This string is 33 characters long') AS string_varchar_datalength;

From whence camest thou?

My Google-Bing-Fu located these functions in the following link.
As well as a few answers on StackOverflow regarding querying SQL Server and A.N.Other datastore.

So now that I think I’ve grasped where they came from, will I be using these going forward?

No, thanks.

Oh, and in case you were wondering how I got the “ODBC date/time extension option” information, good error messages are irreplaceable.

SELECT {date '2020-01-01'} AS sample_date;

‘date’ is not a recognized ODBC date/time extension option.


Using PowerShell for Lazy Maths

Words: 317
Time to Read: ~ 2 minutes

Pre-Coffee Question

I was asked today if I could figure out how many ways I could arrange the following equation to get the number 18?

// Replacing the "?"s with either "+", "-", "*", or "/" how many ways can you get 18?

2 ? 2 ? 2 ? 2 ? 2

Now I’m sure with enough time and effort that I probably could. But if you ask me this before I’ve had any coffee, the best you’re going to get is a dirty look.

Again, that’s the best that you’re going to get…

So I got lazy.

I know PowerShell.

I know that I can create the formula in PowerShell.

I know that I can invoke the formula in PowerShell to get the result.

So with an icy glare at the offending message and a sip at my scalding coffee, I create the script.

The Script

I’m not saying that this script is any good. Like I said, I wrote the script before the caffeine had hidden the tiredness from my brain.

It works, and that was the main thing for me at the time.

$operators = '+', '-', '*', '/'

$Permutations = foreach ($op1 in $operators) {
  foreach ($op2 in $operators) {
    foreach ($op3 in $operators) {
      foreach ($op4 in $operators) {
        $MatsIzHard = [scriptblock]::Create("2 $op1 2 $op2 2 $op3 2 $op4 2")

        [PSCustomObject]@{
          Formula = $MatsIzHard.ToString()
          Result = $MatsIzHard.InvokeReturnAsIs()
        }
      }
    }
  }
}
Defining the script and then calling the `$Permutations variable to get the results.
Like I said, seems to work…

Now that I have the results in the $Permutations variable, I can look for any results where the Result property is 18.

$Permutations.Where({ $_.Result -eq 18 })
2 rows returned from the `$Permutations variable showing 18 as the result
Yes, I can answer your question.

Yes, I can get 18 from that expression 2 ways…

On to my Actual Job

Overall that took around 3 minutes to complete. Which was the perfect time required for the caffeine to kick in and for me to be ready to start my day.

Now, that PowerShell was not pretty, dynamic, or efficient.
It was probably only fast because the number of iterations was so low.

But I’m happy since even though I can’t do maths before coffee, I can at least write PowerShell.