DataCollector SELECT tricks

Date conversions

When importing date fields with the DataCollector, you might run into issues with the date format. There are many ways to represent a date, for example:

• 14-03-2023

• 14-3-2023

• March 14, 2023

• 2023/03/14

• 03/14/2023

Suppose you want to import data from an Excel sheet. The format of dates, as offered by the DataCollector to BlueDolphin, depends on many things: the actual format of the raw data in Excel, the way dates are displayed in Excel (cell formatting), your Microsoft Office language & region settings, and the operating system’s language & region settings.

Typically your SELECT statement in the DataCollector will simply be:

SELECT * FROM [tablename$]

Instead, you should explicitly list all fields (= column names of your Excel) in the SELECT statement and use the Format() function to enforce the correct date format for date fields.

For example:

The example select query is:

SELECT  [Title],
        [Application ID],
        [Application Owner],
        [Application Name],
        [Application Vendor],
        [Application Type],
        [Hosting Type],
        [More Info],
        [Application Description],
        Format([Contract Start Date], "dd-MM-yyyy") AS [Contract Start Date],
        Format([Contract End Date], "dd-MM-yyyy") AS [Contract End Date],
        Format([End-of-Life Date], "dd-MM-yyyy") AS [End-of-Life Date],
        Format([End-of-Service Date], "dd-MM-yyyy") AS [End-of-Service Date],
        [OPEX],
        [CAPEX],
        [Relative Cost Indication],
        [Connectivity],
        [Life Cycle Status]
FROM    [Applications$]

All date values for Contract Start, Contract End, End-of-Life, and End-of-Service, are now explicitly converted to “dd-MM-yyyy” (for example: 14-03-2023). BlueDolphin accepts this date format and data fields are populated correctly.

Filter data before import

Although we recommend to have the data in your leading data source as clean and correct as possible, it might be useful to define filters to selectively exclude data from the import. You can do this by adding logical conditions to the WHERE-clause of the SELECT statement. Some examples:

Only select active applications

SELECT  [Title],
        [Application ID],
        ...
        [Life Cycle Status]
FROM    [Applications$]
WHERE   [Life Cycle Status] = 'Active'

Leave out records that have no application name

SELECT  [Title],
        [Application ID],
        [Application Name],
        ...
FROM    [Applications$]
WHERE   [Application Name] IS NOT NULL

Only select applications above a certain cost threshold

SELECT  [Title],
        [Application ID],
        ...
        [OPEX],
        ...
FROM    [Applications$]
WHERE   [OPEX] >= 10000

Combine it all with logical expressions

SELECT  [Title],
        [Application ID],
        [Application Name],
        ...
        [OPEX],
        ...
        [Life Cycle Status]
FROM    [Applications$]
WHERE   [OPEX] >= 10000 AND
        [Life Cycle Status] = 'Active' AND
        [Application Name] IS NOT NULL

Manipulate data before import

In a similar way, you can manipulate data before importing it. Some examples:

Calculate yearly expenses based on monthly license fees

SELECT  [Title],
        [Application ID],
        ...
        [Monthly License Costs] * 12 AS [OPEX],
        ...
FROM    [Applications$]
WHERE   ...

The source data (e.g. an Excel file) has a column called ‘Monthly License Costs’. This is multiplied by 12 to get yearly operational expenses. The resulting value is stored in a column named ‘OPEX’.

Concatenate text values

SELECT  [Title],
        [Application ID],
        ...
        CONCAT([FirstName], ' ', [LastName]) AS [Application Owner]),
        ...
FROM    [Applications$]
WHERE   ...

Using the CONCAT() function, first and last names are glued together and stored in a column named [Application Owner]. Note the space between the two values.

Further reading

Please check the documentation by W3Schools for further reading. Some pointers:

• SELECT statement: https://www.w3schools.com/sql/sql_where.asp

• WHERE clause: https://www.w3schools.com/sql/sql_where.asp

• Logical expressions: https://www.w3schools.com/sql/sql_and_or.asp

• Functions: https://www.w3schools.com/sql/sql_ref_sqlserver.asp

• Date types: https://www.w3schools.com/sql/sql_dates.asp