PRJ-13
Oct 2024 — April 2026 · a high-mix architectural lighting manufacturer in Montreal
SQL Query Library for Data Diagnostics
Data & SQL · Diagnostics
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