← All projects

PRJ-13

Oct 2024 — April 2026 · a high-mix architectural lighting manufacturer in Montreal

SQL Query Library for Data Diagnostics

Data & SQL · Diagnostics

Data & SQL

Built the key queries as a reusable stored-procedure library on MS SQL Server and documented in Confluence — so anyone in the automation team could run the right procedure for solving the jira ticket in front of them without rewriting SQL.

T-SQL QUERIES IN DATABASE LIBRARY

0→16

LINES OF T-SQL

—→~800

TABLES JOINED PER QUERY

—→up to 18

Problem

Data-related tickets each needed SQL query written from scratch, so diagnostics were slow and inconsistent across the automation team.

Approach

  • Created all of the team's key queries as a reusable library on MS SQL Server, stored as stored procedures in the database and documented in the automation department's Confluence pages, so anyone on the team could run the right procedure for the ticket in front of them without rewriting SQL
  • Wrote 16 T-SQL queries (about 800 lines) using common table expressions, string aggregation, CASE logic and joins across up to about 18 tables — turning normalized configuration data into readable, one-row-per-item results
  • The library covers: fixtures, categories and ordering-code parameters; electrical and mechanical settings by parameter group; compensation rules; optics, mountings and power options; driver picking sets by product; LED board data (parallel connections, chips per line, lengths, revision and usage dates); ordering-code combinations not yet automated or flagged as problematic; and output settings such as PDF pages and display order
  • The MU10 Excel calculator reads from these queries through a live database connection with refreshed tables, so it always works from current data

Outcome

Team members ran the same procedures whenever they handled data-related tickets, and the MU10 calculator always works from current data through the live connection.

Artifacts

Selected outputs from the project — videos, generated drawings, code, and stakeholder decks.

sql

WITH ElectricalSettingGroup_CTE AS (
    SELECT 
        ESG.[ElectricalSettingsGroupID],
        STUFF((
            SELECT ', ' + P2.Code
            FROM [AutomationDB].[ConfigSolver].[ElectricalSettingsGroupMap] AS ESG2
            LEFT JOIN [ConfigCodes].[ParameterMap] AS ParamMap2
                ON ParamMap2.ID = ESG2.ParamMap_ID
            LEFT JOIN ConfigCodes.Parameters AS P2
                ON P2.ID = ParamMap2.ParameterId
            WHERE ESG2.[ElectricalSettingsGroupID] = ESG.[ElectricalSettingsGroupID]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Parameters
    FROM 
        [AutomationDB].[ConfigSolver].[ElectricalSettingsGroupMap] AS ESG
    LEFT JOIN 
        [ConfigCodes].[ParameterMap] AS ParamMap
        ON ParamMap.ID = ESG.ParamMap_ID
    LEFT JOIN 
        ConfigCodes.Parameters AS P
        ON P.ID = ParamMap.ParameterId
    GROUP BY 
        ESG.[ElectricalSettingsGroupID]
)

SELECT
    CES.ID AS 'EmitterSettingID',
	F.Code AS 'Fixture ID',
	CES.[ElectricalSettingsGroupID],
	ESG.Parameters AS 'ParamMapGroup',
    OE.OpticalEfficiency AS 'Optical Efficiency',
    FD.Direction AS 'Direction',
    ETM.Name AS 'Mechanical Type',
	DG.Name AS 'Dictionary Group',
	CN.ChannelName AS 'Channel',
    CES.[FixedLumens],
    CES.[FixedAmperage],
    CASE 
        WHEN CES.[HasMultipleChannels] = 0 THEN 'False'
        WHEN CES.[HasMultipleChannels] = 1 THEN 'True'
    END AS 'HasMultipleChannels',
    CASE 
        WHEN CES.[Default] = 0 THEN 'False'
        WHEN CES.[Default] = 1 THEN 'True'
    END AS 'Default',
    SF.Flag AS 'Solving Flag',
    ER.Name AS 'Emitter Rule',
    CVG.Name AS 'Cartridge Value Group',
    ER.MaxCartridgePerSection AS 'Max Cartridges Per Section',
    ER.CartridgeToLEDInstallationGap AS 'Cartridge To LED Installation Gap',
    ER.InstallationGapBetweenCartridges AS 'Installation Gap Between Cartridges',
    ER.InstallationGapBetweenCartridgeAndEndCap AS 'Installation Gap Between Cartridge And EndCap',
    ER.MaxDarkSpot AS 'Max Dark Spot',
    CASE 
        WHEN ER.[IsReflector] = 0 THEN 'False'
        WHEN ER.[IsReflector] = 1 THEN 'True'
    END AS 'IsReflector',
    ER.AddBlank AS 'Add Blank',
    CASE 
        WHEN ER.[EqualSizeCartridges] = 0 THEN 'False'
        WHEN ER.[EqualSizeCartridges] = 1 THEN 'True'
    END AS 'Equal Size Cartridges',
    ER.MinBlankSize AS 'Min Blank Size',
    ER.DriverReharnessingLength AS 'Driver Reharnessing Length',
    CASE 
        WHEN CES.[IsTroffer] = 0 THEN 'False'
        WHEN CES.[IsTroffer] = 1 THEN 'True'
    END AS 'IsTroffer',
    CES.[BoardsPerFoot],
    CASE 
        WHEN CES.[IsTape] = 0 THEN 'False'
        WHEN CES.[IsTape] = 1 THEN 'True'
    END AS 'IsTape',
    CASE 
        WHEN CES.[UseCartridge] = 0 THEN 'False'
        WHEN CES.[UseCartridge] = 1 THEN 'True'
    END AS 'UseCartridge'
	FROM 
    [AutomationDB].[ConfigSolver].[ElectricalSettingsByCode] AS CES
LEFT JOIN 
    ConfigSolver.OpticalEfficiency AS OE
    ON OE.ID = CES.OpticalEfficiencyId
LEFT JOIN 
    ConfigCodes.Fixtures AS F
    ON F.id = CES.FixtureID
LEFT JOIN 
    AvailableOptions.FixtureDirectionality AS FD
    ON FD.ID = CES.Direction
LEFT JOIN 
    AvailableOptions.EmitterTypesMechanical AS ETM
    ON ETM.ID = CES.EmitterTypeMechanicalID
LEFT JOIN 
    ConfigSolver.DictionaryGroup AS DG
    ON DG.ID = CES.DictionaryGroupID
LEFT JOIN 
    ConfigSolver.EmitterRules AS ER
    ON ER.ID = CES.EmitterRuleID
LEFT JOIN 
    ConfigSolver.SolvingFlags AS SF
    ON SF.ID = CES.SolvingFlag
LEFT JOIN 
    ConfigSolver.CartridgeValueGroups AS CVG
    ON CVG.ID = ER.CartridgeValueGroupID
LEFT JOIN 
    ConfigSolver.ChannelNames AS CN
	ON CN.id = CES.ChannelID
LEFT JOIN 
    ElectricalSettingGroup_CTE AS ESG -- Join with the CTE
    ON CES.[ElectricalSettingsGroupID] = ESG.[ElectricalSettingsGroupID]
ORDER BY 
    EmitterSettingID ASC;

Electrical settings by ordering code — parameter groups aggregated per fixture with string aggregation and CTEs.

Next · PRJ-14

MU13 MikroLite Configurator

→