{"id":1294,"date":"2023-03-04T20:57:28","date_gmt":"2023-03-04T20:57:28","guid":{"rendered":"https:\/\/kpwalters.com\/?page_id=1294"},"modified":"2023-03-04T21:09:34","modified_gmt":"2023-03-04T21:09:34","slug":"sql-gender_pay_gap-alter","status":"publish","type":"page","link":"https:\/\/kpwalters.com\/index.php\/data-analyst\/uk-gender-pay-gap-project\/sql-gender_pay_gap-alter\/","title":{"rendered":"SQL gender_pay_gap ALTER"},"content":{"rendered":"\n<div class=\"wp-block-uagb-container uagb-block-0d02acf4 alignfull uagb-is-root-container\">\n<div class=\"wp-block-uagb-advanced-heading uagb-block-2698b036\"><h5 class=\"uagb-heading-text\">UPDATE and ALTER gender_pay_gap<\/h5><\/div>\n<\/div>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-e35b4ab6 alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-uagb-container uagb-block-4a6759eb default uagb-is-root-container\">\n<pre class=\"wp-block-code\"><code>--* ***************************************************************************************\n-- I created the Gender Pay Gap table with varchars for certain fields because the\n-- original data contained empty values. There were a few alternative solutions, but\n-- I thought it easiest to import as text, update empty values to NULL, then modify\n-- the table.\n--* ***************************************************************************************\n\nUPDATE gender_pay_gap\n\tSET \n\tDiffMeanHourlyPercent = CASE WHEN DiffMeanHourlyPercent = '' THEN NULL ELSE DiffMeanHourlyPercent END\n\t, DiffMedianHourlyPercent = CASE WHEN DiffMedianHourlyPercent = '' THEN NULL ELSE DiffMedianHourlyPercent END\n\t, DiffMeanBonusPercent = CASE WHEN DiffMeanBonusPercent = '' THEN NULL ELSE DiffMeanBonusPercent END\n\t, DiffMedianBonusPercent = CASE WHEN DiffMedianBonusPercent = '' THEN NULL ELSE DiffMedianBonusPercent END\n\t, MaleBonusPercent = CASE WHEN MaleBonusPercent = '' THEN NULL ELSE MaleBonusPercent END\n\t, FemaleBonusPercent = CASE WHEN FemaleBonusPercent = '' THEN NULL ELSE FemaleBonusPercent END\n\t, MaleLowerQuartile = CASE WHEN MaleLowerQuartile = '' THEN NULL ELSE MaleLowerQuartile END\n\t, FemaleLowerQuartile = CASE WHEN FemaleLowerQuartile = '' THEN NULL ELSE FemaleLowerQuartile END\n\t, MaleLowerMiddleQuartile = CASE WHEN MaleLowerMiddleQuartile = '' THEN NULL ELSE MaleLowerMiddleQuartile END\n\t, FemaleLowerMiddleQuartile = CASE WHEN FemaleLowerMiddleQuartile = '' THEN NULL ELSE FemaleLowerMiddleQuartile END\n\t, MaleUpperMiddleQuartile = CASE WHEN MaleUpperMiddleQuartile = '' THEN NULL ELSE MaleUpperMiddleQuartile END\n\t, FemaleUpperMiddleQuartile = CASE WHEN FemaleUpperMiddleQuartile = '' THEN NULL ELSE FemaleUpperMiddleQuartile END\n\t, MaleTopQuartile = CASE WHEN MaleTopQuartile = '' THEN NULL ELSE MaleTopQuartile END\n\t, FemaleTopQuartile = CASE WHEN FemaleTopQuartile = '' THEN NULL ELSE FemaleTopQuartile END\n;\n\n--* ***************************************************************************************\n-- This command modifies the table and converts the varchar fields to numbers.\n--* ***************************************************************************************\nALTER TABLE gender_pay_gap\t\n\tALTER COLUMN DiffMeanHourlyPercent TYPE numeric USING DiffMeanHourlyPercent::numeric\n \t, ALTER COLUMN DiffMedianHourlyPercent TYPE numeric USING DiffMedianHourlyPercent::numeric\n\t, ALTER COLUMN DiffMeanBonusPercent TYPE numeric USING DiffMeanBonusPercent::numeric\n \t, ALTER COLUMN DiffMedianBonusPercent TYPE numeric USING DiffMedianBonusPercent::numeric\n \t, ALTER COLUMN MaleBonusPercent TYPE numeric USING MaleBonusPercent::numeric\n \t, ALTER COLUMN FemaleBonusPercent TYPE numeric USING FemaleBonusPercent::numeric\n \t, ALTER COLUMN MaleLowerQuartile TYPE numeric USING MaleLowerQuartile::numeric\n \t, ALTER COLUMN FemaleLowerQuartile TYPE numeric USING FemaleLowerQuartile::numeric\n \t, ALTER COLUMN MaleLowerMiddleQuartile TYPE numeric USING MaleLowerMiddleQuartile::numeric\n \t, ALTER COLUMN FemaleLowerMiddleQuartile TYPE numeric USING FemaleLowerMiddleQuartile::numeric\n \t, ALTER COLUMN MaleUpperMiddleQuartile TYPE numeric USING MaleUpperMiddleQuartile::numeric\n \t, ALTER COLUMN FemaleUpperMiddleQuartile TYPE numeric USING FemaleUpperMiddleQuartile::numeric\n \t, ALTER COLUMN MaleTopQuartile TYPE numeric USING MaleTopQuartile::numeric\n \t, ALTER COLUMN FemaleTopQuartile TYPE numeric USING FemaleTopQuartile::numeric\n;\n\n\n<\/code><\/pre>\n<\/div>\n<\/div><\/div>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-96126f8f alignfull uagb-is-root-container\"><div class=\"uagb-container-inner-blocks-wrap\">\n<div class=\"wp-block-uagb-container uagb-block-12ad0146\"><\/div>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-ba84afdb\"><\/div>\n\n\n\n<div class=\"wp-block-uagb-container uagb-block-417a5e2e\"><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"<p>UPDATE and ALTER gender_pay_gap<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":971,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"page-builder","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"disabled","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"enabled","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"kevin.p.walters@live.com","author_link":"https:\/\/kpwalters.com\/index.php\/author\/kevin-p-walterslive-com\/"},"uagb_comment_info":0,"uagb_excerpt":"UPDATE and ALTER gender_pay_gap","_links":{"self":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1294"}],"collection":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/comments?post=1294"}],"version-history":[{"count":4,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1294\/revisions"}],"predecessor-version":[{"id":1309,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/1294\/revisions\/1309"}],"up":[{"embeddable":true,"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/pages\/971"}],"wp:attachment":[{"href":"https:\/\/kpwalters.com\/index.php\/wp-json\/wp\/v2\/media?parent=1294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}