Calculate quarter and month in T-SQL

In Uncategorized on 2016/03/08 by phejndorf

A quick note to self for converting quarter to (first) month in quarter and month to quarter by calculation:

Q = (M + 2) / 3

M = (Q * 3) – 2


Use Powershell function parameters like Write-Host

In Powershell on 2016/02/11 by phejndorf

It would seem straightforward, but I’ve spent a couple of hours googling and experimenting to solve the riddle, so here is what I found.

What I wanted to do, was to write a Powershell function with parameters that work like the Write-Host commandlet, where you can have both named parameters (ie –BackgroundColor) and a number of other parameters (words) that are not positional parameters but get collected into a string like so:


I my case I wanted a Text-Out function with an optional –Filename parameter where the output could be appended as well as written to the console like this:

Text-Out Lorem ipsum dolor -Filename "e:\test.txt" sit amet

Here is my Text-Outfunction:

function Text-Out

     [string] $filename,
     [string[]] $remainingargs

    Write-Host ([string]::Join(" ", $remainingargs))

    if (-not $filename) {return}

    Add-Content $filename ([string]::Join(" ", $remainingargs))



The first point to note is the CmdletBinding, this is required in order that Powershell does not interpret the first word as a positional parameter, ie sets $filename = Lorem, in case the filename named parameter has been left out.

Second, the Parameter(ValueFromRemainingArguments=$true) ensures that all the words that are neither positional nor name parameters as such get collected into an array of strings.

Third, these words are then joined with the .Net [string]::Join() function. If the separating spaces were not needed, the built in Powershell –join function could be used instead. If Add-Content was just given the $remaingargs array, it would write one line for each word.


Can’t install Sql Management Studio on Windows 8, .Net 3.5 missing

In Uncategorized on 2015/09/24 by phejndorf

It is really odd that it should be so hard getting SSMS 2014 on a Windows 8 box.

The problem is that SSMS needs .Net 3.5, but that the ordinary methods  to get .Net 3.5 installed (Windows Features in Programs and Features) do not work but only result in some very unhelpful messages!

Kunal Chowdry has made a nice description of the whole thing here, but to cut a long story short here is the gist of it:

DISM  /Online  /Enable-Feature  /FeatureName:NetFx3  /All  /LimitAccess  /Source:x:\sources\sxs

You run from an administrative command prompt – remember to replace “x:” whith the path to your Windows 8 installation path, ie a DVD.

DISM (Deployment Image Servicing and Management) has come to my help before. So sad that Windows has always lacked proper package management, so we need to rely on a mix of Control Panel, DISM, Installers, Chocolatey, Boxstarter etc.


T-SQL UNPIVOT’ing columns with different datatypes

In Uncategorized on 2015/09/01 by phejndorf

Well, you can’t, really. What you have to do is to make sure that all the the columns have the same datatype and then perform the unpivot.

Here is the table I want to partially unpivot:


So what I do is to cast all columns to a suitable common format, in this case VARCHAR(100), before the actual pivoting:

SELECT Id, ColumnName, Value
        CAST(SysNr AS Varchar(100)) SysNr,
        CAST(KredsNr AS Varchar(100)) KredsNr,    
        CAST(EjendomsNr AS Varchar(100)) EjendomsNr,    
        CAST(Navn AS Varchar(100)) Navn,
        CAST(EjendomStatusId AS Varchar(100)) EjendomStatusId,
        CAST(Aktiv AS Varchar(100)) Aktiv
    FROM Ejendom
) AS S
    Value FOR ColumnName IN (SysNr, KredsNr, EjendomsNr, Navn, EjendomStatusId, Aktiv)
) AS U

Voila – quite a bit of typing, but pretty easy peasy otherwise.


Switching your IP setting to static with PowerShell

In Uncategorized on 2015/08/19 by phejndorf


In our Hyper-V test environment we use a mixture of dynamic and static IP addresses. Some servers need to have static IP addresses and in order to facilitate the process of provisioning a new server I wanted to have a script that toggles the current network adapter setting from dynamic (DHCP) to static.

Do remember to mark that IP in the DHCP server as unavailable as well in order not to get IP confilcts…

Write-Host "Switch Current IP configuration to to Static IP"

$wmiip = Get-WmiObject win32_networkadapterconfiguration -filter "DHCPEnabled = 'true' and ipenabled = 'true'"

if ($wmiip)
    Write-Host "Switching..."

    $ipaddress = $wmiip.IPAddress[0] 
    $ipgateway = $wmiip.DefaultIPGateway[0]
    $ipdnsordr = $wmiip.DNSServerSearchOrder

    $wmiip.EnableStatic($ipaddress, "")
    $wmiip.SetGateWays($ipgateway, 1)
    Write-Host "Static already set"

Get-WmiObject win32_networkadapterconfiguration -filter "ipenabled = 'true'"
"DNS              : " + $wmiip.DNSServerSearchOrder

Write-Host "Done..." 


This script was inspired by an article by Scripting Guy


T-Sql to generate a table of datetimes (time series)

In Uncategorized on 2015/04/16 by phejndorf

The other day I needed a table of datetimes for each second for a given period, and investigated various suggestions of which there are many around the web.

What I came up with as the, in my opinion, easiest solution was this code. I already had a very large table that could provide me with a set of records to iterate over (millions), avoiding some complicated T-Sql looping.

        ROW_NUMBER() OVER (ORDER BY Id) AS Number,
            ROW_NUMBER() OVER (ORDER BY Id), 
            DATEDIFF(DAY, 0, GETDATE()))) AS DateTime
        FROM [DB]..[LargeTable]

The number 86399 corresponds to  the number of seconds (One day = (60*60*24) – 1) to generate in the table.


2014 in review

In Uncategorized on 2014/12/30 by phejndorf

The stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about 18,000 times in 2014. If it were a concert at Sydney Opera House, it would take about 7 sold-out performances for that many people to see it.

Click here to see the complete report.