--* ***************************************************************************************
-- I created the Gender Pay Gap table with varchars for certain fields because the
-- original data contained empty values. There were a few alternative solutions, but
-- I thought it easiest to import as text, update empty values to NULL, then modify
-- the table.
--* ***************************************************************************************

UPDATE gender_pay_gap
	SET 
	DiffMeanHourlyPercent = CASE WHEN DiffMeanHourlyPercent = '' THEN NULL ELSE DiffMeanHourlyPercent END
	, DiffMedianHourlyPercent = CASE WHEN DiffMedianHourlyPercent = '' THEN NULL ELSE DiffMedianHourlyPercent END
	, DiffMeanBonusPercent = CASE WHEN DiffMeanBonusPercent = '' THEN NULL ELSE DiffMeanBonusPercent END
	, DiffMedianBonusPercent = CASE WHEN DiffMedianBonusPercent = '' THEN NULL ELSE DiffMedianBonusPercent END
	, MaleBonusPercent = CASE WHEN MaleBonusPercent = '' THEN NULL ELSE MaleBonusPercent END
	, FemaleBonusPercent = CASE WHEN FemaleBonusPercent = '' THEN NULL ELSE FemaleBonusPercent END
	, MaleLowerQuartile = CASE WHEN MaleLowerQuartile = '' THEN NULL ELSE MaleLowerQuartile END
	, FemaleLowerQuartile = CASE WHEN FemaleLowerQuartile = '' THEN NULL ELSE FemaleLowerQuartile END
	, MaleLowerMiddleQuartile = CASE WHEN MaleLowerMiddleQuartile = '' THEN NULL ELSE MaleLowerMiddleQuartile END
	, FemaleLowerMiddleQuartile = CASE WHEN FemaleLowerMiddleQuartile = '' THEN NULL ELSE FemaleLowerMiddleQuartile END
	, MaleUpperMiddleQuartile = CASE WHEN MaleUpperMiddleQuartile = '' THEN NULL ELSE MaleUpperMiddleQuartile END
	, FemaleUpperMiddleQuartile = CASE WHEN FemaleUpperMiddleQuartile = '' THEN NULL ELSE FemaleUpperMiddleQuartile END
	, MaleTopQuartile = CASE WHEN MaleTopQuartile = '' THEN NULL ELSE MaleTopQuartile END
	, FemaleTopQuartile = CASE WHEN FemaleTopQuartile = '' THEN NULL ELSE FemaleTopQuartile END
;

--* ***************************************************************************************
-- This command modifies the table and converts the varchar fields to numbers.
--* ***************************************************************************************
ALTER TABLE gender_pay_gap	
	ALTER COLUMN DiffMeanHourlyPercent TYPE numeric USING DiffMeanHourlyPercent::numeric
 	, ALTER COLUMN DiffMedianHourlyPercent TYPE numeric USING DiffMedianHourlyPercent::numeric
	, ALTER COLUMN DiffMeanBonusPercent TYPE numeric USING DiffMeanBonusPercent::numeric
 	, ALTER COLUMN DiffMedianBonusPercent TYPE numeric USING DiffMedianBonusPercent::numeric
 	, ALTER COLUMN MaleBonusPercent TYPE numeric USING MaleBonusPercent::numeric
 	, ALTER COLUMN FemaleBonusPercent TYPE numeric USING FemaleBonusPercent::numeric
 	, ALTER COLUMN MaleLowerQuartile TYPE numeric USING MaleLowerQuartile::numeric
 	, ALTER COLUMN FemaleLowerQuartile TYPE numeric USING FemaleLowerQuartile::numeric
 	, ALTER COLUMN MaleLowerMiddleQuartile TYPE numeric USING MaleLowerMiddleQuartile::numeric
 	, ALTER COLUMN FemaleLowerMiddleQuartile TYPE numeric USING FemaleLowerMiddleQuartile::numeric
 	, ALTER COLUMN MaleUpperMiddleQuartile TYPE numeric USING MaleUpperMiddleQuartile::numeric
 	, ALTER COLUMN FemaleUpperMiddleQuartile TYPE numeric USING FemaleUpperMiddleQuartile::numeric
 	, ALTER COLUMN MaleTopQuartile TYPE numeric USING MaleTopQuartile::numeric
 	, ALTER COLUMN FemaleTopQuartile TYPE numeric USING FemaleTopQuartile::numeric
;