#PSTip Functions and SQL filtering, Part 4

Note: This tip requires PowerShell 3.0 or later.

Filtering information retrieved from SQL database should not be limited to text filters only. Sometimes we may want to use dates to filter out some records. Other times we may want to get only records with some numeric value higher/lower than value specified. To cover wider range of filters we will need a bit smarter logic for producing complete query.

Let’s start with dates. Our database has two fields that contain DateTime value: StartDate and EndDate. Suppose we want to have switches that will cover situations when date is either in the future or in the past:

param (
    # ... other parameters ...
    # Retrieves new-hire employees.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$BeforeStartDate,

    # Retrieves currently hired employees.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$BeforeEndDate,

    # Retrieves employees that were already hired.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$AfterStartDate,

    # Retrieves laid off employees.
    [Parameter(
        ParameterSetName = 'list'
    )]
    [switch]$AfterEndDate
)

All parameters match the same pattern: time relation (After/Before) followed by table column name. Therefore we will use -Regex switch to identify either scenario. As we still want to be able to support our ‘translated’ columns, we will need to generate appropriate pattern for that scenario too:

$mappedParameters = '^({0})$' -f ($parameterMap.Keys -join '|')
$queryList = New-Object System.Collections.ArrayList
switch -Regex ($PSBoundParameters.Keys) {
    $mappedParameters {
        $wildcard = [System.Management.Automation.WildcardPattern]$PSBoundParameters[$_]
        $wql = $wildcard.ToWql()
        $item = "[{0}] LIKE '{1}'" -f $parameterMap[$_], $wql
        $queryList.Add($item) | Out-Null
    }
    ^Before {
        $field = $_ -replace '^Before'
        $item = "[{0}] > '{1}'" -f $field, (Get-Date)
        $queryList.Add($item) | Out-Null
    }

    ^After {
        $field = $_ -replace '^After'
        $item = "[{0}] < '{1}'" -f $field, (Get-Date)
        $queryList.Add($item) | Out-Null
    }
}

There is also nothing that would prevent us from doing both things at the same time: use different filter than LIKE and be able to map SQL columns to friendly parameters/properties of output object. For example: we add two parameters that enable filtering on Id property (that maps to ‘Employee Id’ column): IdLowerThan and IdGreaterThan. Code that would create appropriate filter for us:

switch -Regex ($PSBoundParameters.Keys) {
    # ...
    GreaterThan$ {
        $fieldMappedTo = $_ -replace 'GreaterThan$'
        $field = $parameterMap[$fieldMappedTo]
        $item = '[{0}] > {1}' -f $field, $PSBoundParameters[$_]
        $queryList.Add($item) | Out-Null
    }
    LowerThan$ {
        $fieldMappedTo = $_ -replace 'LowerThan$'
        $field = $parameterMap[$fieldMappedTo]
        $item = '[{0}] < {1}' -f $field, $PSBoundParameters[$_]
        $queryList.Add($item) | Out-Null
    }
}

Using switch statement makes it easy to extend our reach for any scenario we can think of. We just need to be sure that we name our parameters in a way that makes it possible to identify all options.

Note: Complete function that has all the bells and whistles can be found here.

Example use case: find new hires that have Id lower than 7:

Get-EmployeeData -BeforeStartDate -IdLowerThan 7 |
    Format-Table -AutoSize

Department StartDate          EndDate            SamAccountName Description            Id Surname Title            GivenName
---------- ---------          -------            -------------- -----------            -- ------- -----            ---------
Facilities 10-Jan-15 00:00:00 04-Jan-22 00:00:00 ABeans         Security in building 4  6 Beans   Security Officer Adam