{"id":3785,"date":"2026-04-23T17:02:21","date_gmt":"2026-04-23T17:02:21","guid":{"rendered":"https:\/\/jpbydesign.com\/?p=3785"},"modified":"2026-04-24T21:16:22","modified_gmt":"2026-04-24T21:16:22","slug":"sql-example-workforce-grid","status":"publish","type":"post","link":"https:\/\/jpbydesign.com\/index.php\/2026\/04\/23\/sql-example-workforce-grid\/","title":{"rendered":"SQL Example Workforce Grid"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

SQL code<\/h3>\n\n\n\n
\/*\n\tWorkforce Grid\n\tCreated by John Horton of Logic Intelligence\n\tUpdated 12\/9\/2025\n*\/<\/mark>\nWITH<\/mark> stage_arch_base\nAS<\/mark> (\n\t\/*extract architect values from table*\/<\/mark>\n\tSELECT <\/mark>portal_id\n\t\t,processinstance_id\n\t\t,rownumber\n\t\t,dynamicgrid_name\n\t\t,Portal_id || '|' || processinstance_id || '|' || rownumber AS<\/mark> Rowkey\n\t\t-- person \/ role fields<\/mark>\n\t\t,CASE \n\t\t\tWHEN <\/mark>customfield_name IN<\/mark> ('Company Name')\n\t\t\t\tTHEN <\/mark>customfieldvalue\n\t\t\tEND AS<\/mark> subcontractor_name\n\t\t,CASE \n\t\t\tWHEN<\/mark> customfield_name IN<\/mark> ('Position Title')\n\t\t\t\tTHEN<\/mark> customfieldvalue\n\t\t\tWHEN<\/mark> customfield_name IN<\/mark> ('Trade or Specialization')\n\t\t\t\tAND NOT<\/mark> customfieldvalue ~ '^[0-9]{1}'\n\t\t\t\tTHEN<\/mark> customfieldvalue\n\t\t\tEND AS<\/mark> Craft\n\t\t,CASE \n\t\t\tWHEN<\/mark> customfield_name = 'IsAPP'\n\t\t\t\tTHEN<\/mark> customfieldvalue\n\t\t\tEND AS<\/mark> trade_stage\n\t\t,CASE \n\t\t\tWHEN<\/mark> charindex<\/mark>('Female', customfield_name) > 0\n\t\t\t\tTHEN <\/mark>'Female'\n\t\t\tWHEN charindex<\/mark>('Male', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'Male'\n\t\t\tWHEN charindex<\/mark>('Non-Binary', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'Non-Binary'\n\t\t\tELSE NULL<\/mark>\n\t\t\tEND AS<\/mark> Gender\n\t\t,CASE \n\t\t\tWHEN charindex<\/mark>('Caucasian', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'Caucasian'\n\t\t\tWHEN charindex<\/mark>('African-American\/Black', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'African American'\n\t\t\tWHEN charindex<\/mark>('Hispanic', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'Hispanic'\n\t\t\tWHEN charindex<\/mark>('Other', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'Other'\n\t\t\tWHEN charindex<\/mark>('Asian\/Pacific Islander', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'Asian\/Pacific Islander'\n\t\t\tWHEN charindex<\/mark>('Native American', customfield_name) > 0\n\t\t\t\tTHEN<\/mark> 'Native American'\n\t\t\tELSE NULL\n\t\t\tEND AS<\/mark> Race\n\t\t,customfield_name\n\t\t,customfieldvalue\n\tFROM <\/mark>\"SomeDataConnection\".\"processinstancedynamicgrid_fact\"\n\tWHERE<\/mark> dynamicgrid_name IN<\/mark> ('Architect Utilization')\n),\nsubstage_arch_Join AS<\/mark> (\n\t\/*sub stage to get fields that need to be applied at rowkey level*\/<\/mark>\n\tSELECT MAX<\/mark>(subcontractor_name) AS<\/mark> sub_name\n\t\t,MAX<\/mark>(craft) AS <\/mark>myCraft\n\t\t,Rowkey\n\tFROM<\/mark> stage_arch_base\n\tWHERE<\/mark> subcontractor_name IS NOT NULL<\/mark>\n\t\tOR<\/mark> craft IS NOT NULL<\/mark>\n\tGROUP BY<\/mark> Rowkey\n),\nstage_arch_join AS<\/mark> (\n\t\/*Append fields that are applied across rowkey level*\/<\/mark>\n\tSELECT <\/mark>st.*\n\t\t,gs.sub_name\n\t\t,gs.myCraft\n\tFROM<\/mark> stage_arch_base st\n\tLEFT OUTER JOIN<\/mark> substage_arch_Join gs ON<\/mark> st.Rowkey = gs.Rowkey\n),\nstage_arch_filter AS<\/mark> (\n\t\/*filter records that are not hour values*\/<\/mark>\n\tSELECT<\/mark> portal_id\n\t\t,processinstance_id\n\t\t,rownumber\n\t\t,dynamicgrid_name\n\t\t,Rowkey\n\t\t,sub_name AS<\/mark> subcontractor_name\n\t\t,myCraft AS<\/mark> Craft\n\t\t,trade_stage\n\t\t,gender\n\t\t,race\n\t\t,try_cast<\/mark>(customfieldvalue AS<\/mark> DECIMAL(18, 2)) AS<\/mark> hours\n\tFROM<\/mark> stage_arch_join\n\tWHERE<\/mark> customfield_name NOT IN<\/mark> (\n\t\t\t'Position Title'\n\t\t\t,'Company Name'\n\t\t\t,'Trade or Specialization'\n\t\t\t)\n\t\tAND try_cast<\/mark>(customfieldvalue AS<\/mark> DECIMAL(18, 2)) > 0\n),\nstage_cont_base AS<\/mark> (\n\t\/*extract contributor values from table*\/<\/mark>\n\tSELECT<\/mark> portal_id\n\t\t,processinstance_id\n\t\t,rownumber\n\t\t,dynamicgrid_name\n\t\t,Portal_id || '|' || processinstance_id || '|' || rownumber AS<\/mark> Rowkey\n\t\t-- person \/ role fields<\/mark>\n\t\t,CASE \n\t\t\tWHEN<\/mark> customfield_name IN ('Name')\n\t\t\t\tTHEN<\/mark> customfieldvalue\n\t\t\tEND AS<\/mark> subcontractor_name\n\t\t,CASE \n\t\t\tWHEN <\/mark>customfield_name IN ('Craft')\n\t\t\t\tTHEN<\/mark> customfieldvalue\n\t\t\tEND AS<\/mark> Craft\n\t\t,CASE \n\t\t\tWHEN <\/mark>customfield_name = 'IsAPP'\n\t\t\t\tTHEN<\/mark> customfieldvalue\n\t\t\tEND AS<\/mark> trade_stage\n\t\t,CASE \n\t\t\tWHEN <\/mark>customfield_name = 'Gender'\n\t\t\t\tTHEN<\/mark> customfieldvalue\n\t\t\tEND AS <\/mark>Gender\n\t\t,CASE \n\t\t\tWHEN <\/mark>customfield_name = 'cau_total_hours'\n\t\t\t\tTHEN<\/mark> 'Caucasian'\n\t\t\tWHEN<\/mark> customfield_name = 'cau_total_hours2'\n\t\t\t\tTHEN<\/mark> 'African-American\/Black'\n\t\t\tWHEN <\/mark>customfield_name = 'cau_total_hours4'\n\t\t\t\tTHEN <\/mark>'Hispanic'\n\t\t\tWHEN<\/mark> customfield_name = 'cau_total_hours6'\n\t\t\t\tTHEN<\/mark> 'Asian'\n\t\t\tWHEN<\/mark> customfield_name = 'cau_total_hours8'\n\t\t\t\tTHEN<\/mark> 'Other'\n\t\t\tWHEN<\/mark> customfield_name = 'cau_total_hours9'\n\t\t\t\tTHEN<\/mark> 'Native American'\n\t\t\tWHEN<\/mark> customfield_name = 'cau_total_hours10'\n\t\t\t\tTHEN<\/mark> 'Multiple'\n\t\t\tWHEN<\/mark> customfield_name = 'cau_total_hours11'\n\t\t\t\tTHEN<\/mark> 'Not Specified'\n\t\t\tELSE NULL\n\t\t\tEND AS<\/mark> race\n\t\t,customfield_name\n\t\t,customfieldvalue\n\tFROM <\/mark>\"SomeDataConnection\".\"processinstancedynamicgrid_fact\"\n\tWHERE<\/mark> dynamicgrid_name IN<\/mark> ('Contractor Workforce Utilization')\n),\nsubstage_cont_Join AS<\/mark> (\n\t\/*sub stage to get fields that need to be applied at rowkey level*\/<\/mark>\n\tSELECT MAX<\/mark>(subcontractor_name) AS<\/mark> sub_name\n\t\t,MAX<\/mark>(craft) AS<\/mark> sub_craft\n\t\t,MAX<\/mark>(trade_stage) AS<\/mark> sub_trade\n\t\t,MAX<\/mark>(gender) AS <\/mark> sub_gender\n\t\t,Rowkey\n\tFROM<\/mark> stage_cont_base\n\tWHERE<\/mark> subcontractor_name IS NOT NULL<\/mark>\n\t\tOR<\/mark> craft IS NOT NULL<\/mark>\n\t\tOR<\/mark> trade_stage IS NOT NULL<\/mark>\n\t\tOR<\/mark> gender IS NOT NULL<\/mark>\n\tGROUP BY<\/mark> Rowkey\n),\nstage_cont_join AS<\/mark> (\n\t\/*Append fields that are applied across rowkey level*\/<\/mark>\n\tSELECT<\/mark> st.*\n\t\t,gs.sub_name\n\t\t,gs.sub_craft\n\t\t,gs.sub_trade\n\t\t,gs.sub_gender\n\tFROM<\/mark> stage_cont_base st\n\tLEFT OUTER JOIN<\/mark> substage_cont_Join gs ON<\/mark> st.Rowkey = gs.Rowkey\n),\nstage_cont_filter AS<\/mark> (\n\t\/*filter records that are not hour values*\/<\/mark>\n\tSELECT <\/mark>portal_id\n\t\t,processinstance_id\n\t\t,rownumber\n\t\t,dynamicgrid_name\n\t\t,Rowkey\n\t\t,sub_name AS subcontractor_name\n\t\t,sub_craft AS Craft\n\t\t,sub_trade AS trade_stage\n\t\t,sub_gender AS gender\n\t\t,race\n\t\t,try_cast<\/mark>(customfieldvalue AS DECIMAL(18, 2)) AS<\/mark> hours\n\tFROM<\/mark> stage_cont_join\n\tWHERE<\/mark> customfield_name NOT IN<\/mark> (\n\t\t\t'Name'\n\t\t\t,'Craft'\n\t\t\t,'IsAPP'\n\t\t\t,'Gender'\n\t\t\t)\n\t\tAND try_cast<\/mark>(customfieldvalue AS DECIMAL(18, 2)) > 0\n)\n\/*Append architect data and contributor together*\/<\/mark>\nSELECT<\/mark> *\nFROM <\/mark>stage_arch_filter\n\nUNION ALL<\/mark>\n\nSELECT <\/mark>*\nFROM <\/mark>stage_cont_filter\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":3792,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"nf_dc_page":"","_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[4,89],"tags":[47,101],"class_list":["post-3785","post","type-post","status-publish","format-standard","has-post-thumbnail","category-database","category-professional-portfolio","tag-sql","tag-sql-code","czr-hentry"],"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7Ze6V-Z3","jetpack-related-posts":[],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/jpbydesign.com\/wp-content\/uploads\/2026\/04\/sql-seeklogo.png?fit=2000%2C1027&ssl=1","_links":{"self":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts\/3785","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/comments?post=3785"}],"version-history":[{"count":5,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts\/3785\/revisions"}],"predecessor-version":[{"id":3793,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/posts\/3785\/revisions\/3793"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/media\/3792"}],"wp:attachment":[{"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/media?parent=3785"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/categories?post=3785"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jpbydesign.com\/index.php\/wp-json\/wp\/v2\/tags?post=3785"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}