SQL Example Workforce Grid

I worked on this for a government related agency – for their construction department. The need was to pivot source data- a very wide, hard to decipher column data – into hours data by craft, trade, gender, race and by a derived row key (a tall, narrow data table). There are 2 sections to the source: arch (Architects) and cont (Contractors) and each has their subset of columns. I combined/union-ed the 2 datasets.

SQL code

/*
	Workforce Grid
	Created by John Horton of Logic Intelligence
	Updated 12/9/2025
*/
WITH stage_arch_base
AS (
	/*extract architect values from table*/
	SELECT portal_id
		,processinstance_id
		,rownumber
		,dynamicgrid_name
		,Portal_id || '|' || processinstance_id || '|' || rownumber AS Rowkey
		-- person / role fields
		,CASE 
			WHEN customfield_name IN ('Company Name')
				THEN customfieldvalue
			END AS subcontractor_name
		,CASE 
			WHEN customfield_name IN ('Position Title')
				THEN customfieldvalue
			WHEN customfield_name IN ('Trade or Specialization')
				AND NOT customfieldvalue ~ '^[0-9]{1}'
				THEN customfieldvalue
			END AS Craft
		,CASE 
			WHEN customfield_name = 'IsAPP'
				THEN customfieldvalue
			END AS trade_stage
		,CASE 
			WHEN charindex('Female', customfield_name) > 0
				THEN 'Female'
			WHEN charindex('Male', customfield_name) > 0
				THEN 'Male'
			WHEN charindex('Non-Binary', customfield_name) > 0
				THEN 'Non-Binary'
			ELSE NULL
			END AS Gender
		,CASE 
			WHEN charindex('Caucasian', customfield_name) > 0
				THEN 'Caucasian'
			WHEN charindex('African-American/Black', customfield_name) > 0
				THEN 'African American'
			WHEN charindex('Hispanic', customfield_name) > 0
				THEN 'Hispanic'
			WHEN charindex('Other', customfield_name) > 0
				THEN 'Other'
			WHEN charindex('Asian/Pacific Islander', customfield_name) > 0
				THEN 'Asian/Pacific Islander'
			WHEN charindex('Native American', customfield_name) > 0
				THEN 'Native American'
			ELSE NULL
			END AS Race
		,customfield_name
		,customfieldvalue
	FROM "SomeDataConnection"."processinstancedynamicgrid_fact"
	WHERE dynamicgrid_name IN ('Architect Utilization')
),
substage_arch_Join AS (
	/*sub stage to get fields that need to be applied at rowkey level*/
	SELECT MAX(subcontractor_name) AS sub_name
		,MAX(craft) AS myCraft
		,Rowkey
	FROM stage_arch_base
	WHERE subcontractor_name IS NOT NULL
		OR craft IS NOT NULL
	GROUP BY Rowkey
),
stage_arch_join AS (
	/*Append fields that are applied across rowkey level*/
	SELECT st.*
		,gs.sub_name
		,gs.myCraft
	FROM stage_arch_base st
	LEFT OUTER JOIN substage_arch_Join gs ON st.Rowkey = gs.Rowkey
),
stage_arch_filter AS (
	/*filter records that are not hour values*/
	SELECT portal_id
		,processinstance_id
		,rownumber
		,dynamicgrid_name
		,Rowkey
		,sub_name AS subcontractor_name
		,myCraft AS Craft
		,trade_stage
		,gender
		,race
		,try_cast(customfieldvalue AS DECIMAL(18, 2)) AS hours
	FROM stage_arch_join
	WHERE customfield_name NOT IN (
			'Position Title'
			,'Company Name'
			,'Trade or Specialization'
			)
		AND try_cast(customfieldvalue AS DECIMAL(18, 2)) > 0
),
stage_cont_base AS (
	/*extract contributor values from table*/
	SELECT portal_id
		,processinstance_id
		,rownumber
		,dynamicgrid_name
		,Portal_id || '|' || processinstance_id || '|' || rownumber AS Rowkey
		-- person / role fields
		,CASE 
			WHEN customfield_name IN ('Name')
				THEN customfieldvalue
			END AS subcontractor_name
		,CASE 
			WHEN customfield_name IN ('Craft')
				THEN customfieldvalue
			END AS Craft
		,CASE 
			WHEN customfield_name = 'IsAPP'
				THEN customfieldvalue
			END AS trade_stage
		,CASE 
			WHEN customfield_name = 'Gender'
				THEN customfieldvalue
			END AS Gender
		,CASE 
			WHEN customfield_name = 'cau_total_hours'
				THEN 'Caucasian'
			WHEN customfield_name = 'cau_total_hours2'
				THEN 'African-American/Black'
			WHEN customfield_name = 'cau_total_hours4'
				THEN 'Hispanic'
			WHEN customfield_name = 'cau_total_hours6'
				THEN 'Asian'
			WHEN customfield_name = 'cau_total_hours8'
				THEN 'Other'
			WHEN customfield_name = 'cau_total_hours9'
				THEN 'Native American'
			WHEN customfield_name = 'cau_total_hours10'
				THEN 'Multiple'
			WHEN customfield_name = 'cau_total_hours11'
				THEN 'Not Specified'
			ELSE NULL
			END AS race
		,customfield_name
		,customfieldvalue
	FROM "SomeDataConnection"."processinstancedynamicgrid_fact"
	WHERE dynamicgrid_name IN ('Contractor Workforce Utilization')
),
substage_cont_Join AS (
	/*sub stage to get fields that need to be applied at rowkey level*/
	SELECT MAX(subcontractor_name) AS sub_name
		,MAX(craft) AS sub_craft
		,MAX(trade_stage) AS sub_trade
		,MAX(gender) AS  sub_gender
		,Rowkey
	FROM stage_cont_base
	WHERE subcontractor_name IS NOT NULL
		OR craft IS NOT NULL
		OR trade_stage IS NOT NULL
		OR gender IS NOT NULL
	GROUP BY Rowkey
),
stage_cont_join AS (
	/*Append fields that are applied across rowkey level*/
	SELECT st.*
		,gs.sub_name
		,gs.sub_craft
		,gs.sub_trade
		,gs.sub_gender
	FROM stage_cont_base st
	LEFT OUTER JOIN substage_cont_Join gs ON st.Rowkey = gs.Rowkey
),
stage_cont_filter AS (
	/*filter records that are not hour values*/
	SELECT portal_id
		,processinstance_id
		,rownumber
		,dynamicgrid_name
		,Rowkey
		,sub_name AS subcontractor_name
		,sub_craft AS Craft
		,sub_trade AS trade_stage
		,sub_gender AS gender
		,race
		,try_cast(customfieldvalue AS DECIMAL(18, 2)) AS hours
	FROM stage_cont_join
	WHERE customfield_name NOT IN (
			'Name'
			,'Craft'
			,'IsAPP'
			,'Gender'
			)
		AND try_cast(customfieldvalue AS DECIMAL(18, 2)) > 0
)
/*Append architect data and contributor together*/
SELECT *
FROM stage_arch_filter

UNION ALL

SELECT *
FROM stage_cont_filter