--	1	StateFips	L	
insert into StateFips
select stfips, stname, abbrev 
from wid28.dbo.stfipstb

--	2	AreaTypes	L	
insert into AreaTypes
values ('00','41','BLS CPI areas'),
('00','00','US'),
('01','01','State'),
('01','03','SDA'),
('01','04','County'),
('01','05','Minor Civil Division'),
('01','06','BLS Region'),
('01','07','Broad Geographic Area (BGA)'),
('01','08','Economic Development Region'),
('01','09','Planning Region'),
('01','10','Labor Market Area'),
('01','11','City'),
('01','12','Town'),
('01','13','Township'),
('01','14','Municipality/Suburb'),
('01','15','Workforce Investment Region'),
('01','16','One Stop Area'),
('01','17','Workforce Development Area'),
('01','18','Job Center Area'),
('01','19','Congressional District'),
('01','20','Census Places'),
('01','25','Metropolitan New England City and Town Area (NECTA)'),
('01','26','Micropolitan New England City and Town Area (NECTA)'),
('01','27','New England City and Town Area (NECTA) Divisions'),
('01','28','Combined New England City and Town Area (NECTA)'),
('01','30','Balance of State'),
('01','31','Metropolitan Statistical Area'),
('01','32','Micropolitan Statistical Area'),
('01','33','Metropolitan Division'),
('01','34','Combined Statistical Area'),
('01','35','EEO County Group'),
('01','41','BLS CPI areas')

insert into AreaTypes
select stfips.stfips, typs.areatype, typs.areatypename 
from (select * from areatypes where stfips='01') typs
cross join (select stfips from Statefips where stfips not in ('00','01')) stfips


--	2	AreaTypeVersions	L	
insert into AreaTypeVersions
values ('00','41','2010',''),
('00','41','2018','Current version'),
('00','00','0000',''),
('01','01','0000',''),
('01','03','0000',''),
('01','04','0000',''),
('01','05','0000',''),
('01','06','0000',''),
('01','07','0000',''),
('01','08','0000',''),
('01','09','0000',''),
('01','10','0000',''),
('01','11','0000',''),
('01','12','0000',''),
('01','13','0000',''),
('01','14','0000',''),
('01','15','0000',''),
('01','16','0000',''),
('01','17','0000',''),
('01','18','0000',''),
('01','19','0000',''),
('01','20','0000',''),
('01','25','2010','Current version'),
('01','26','2010','Current version'),
('01','27','2010','Current version'),
('01','28','2010','Current version'),
('01','30','0000',''),
('01','31','2017','Current version'),
('01','32','2017','Current version'),
('01','33','2017','Current version'),
('01','34','2017','Current version'),
('01','31','2010',''),
('01','32','2010',''),
('01','33','2010',''),
('01','34','2010',''),
('01','35','0000',''),
('01','41','2010',''),
('01','41','2018','Current version'),
('01','31','2003',''),
('01','32','2003',''),
('01','33','2003',''),
('01','34','2003','')

insert into AreaTypeVersions
select stfips.stfips, versions.areatype, versions.areatypeversion, versions.areatypeversionnotes 
from (select * from areatypeversions where stfips='01') versions
cross join (select stfips from Statefips where stfips not in ('00','01')) stfips

--	3	GeoPrecisionCodes	L	
insert into GeoPrecisionCodes
select * from wid28.dbo.geocode

--	4	Geographies	L	
insert into Geographies
select stfips, 
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
	   area,
	   case when areaname is null then '' else areaname end as areaname,
	   areadesc,
	   Latitude,
	   Longitude,
	   Geopcode
from wid28.dbo.geog 
where areatype in ('00','01','04','31','32','33','34','41') 


--	5	SubGeographies	L	
insert into SubGeographies
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area,
		substfips,
		case when subareatyp = '40' then '41'
			when subareatyp = '21' then '31'
			when subareatyp = '22' then '32'
			when subareatyp = '23' then '33'
			when subareatyp = '24' then '34'
			else subareatyp
			end as subareatype,
	   case when subareatyp = '40' then '2010'
			when subareatyp = '41' then '2018'
			when subareatyp = '21' then '2010'
			when subareatyp = '22' then '2010'
			when subareatyp = '23' then '2010'
			when subareatyp = '24' then '2010'
			when subareatyp = '31' then '2017'
			when subareatyp = '32' then '2017'
			when subareatyp = '33' then '2017'
			when subareatyp = '34' then '2017'
			else '0000'
			end as subareatypeversion,
		subarea
from wid28.dbo.subgeog
where areatype in ('00','01','04','31','32','33','34','41') 
and subareatyp in ('00','01','04','31','32','33','34','41') 

--	6	CodeFlags	L	
insert into CodeFlags
values ('0', 'No flag'),
	   ('1', 'OEWS-specific occupation or industry'),
	   ('2', 'State-specific occupation or industry')

--	7	PeriodYears	L	
insert into PeriodYears
select distinct periodyear from wid28.dbo.period

--	8	PeriodTypes	L	
insert into PeriodTypes
select * from wid28.dbo.periodty where periodtype not in ('50','70') --remove state-defined placeholders

--	9	Periods	L	
insert into Periods
values ('01','00','Annual'),
('02','01','First Quarter'),
('02','02','Second Quarter'),
('02','03','Third Quarter'),
('02','04','Fourth Quarter'),
('03','01','January'),
('03','02','February'),
('03','03','March'),
('03','04','April'),
('03','05','May'),
('03','06','June'),
('03','07','July'),
('03','08','August'),
('03','09','September'),
('03','10','October'),
('03','11','November'),
('03','12','December'),
('05','00','Decennial'),
('10','00','1-year ACS'),
('60','00','5-year ACS')

--	12	OccCodeTypes	L	
insert into OccCodeTypes
select * from wid28.dbo.occtypes

insert into OccCodeTypes
values('22', 'Pathways Code')

--	10	CareerClusters	L	
insert into CareerClusters
select '01', * from wid28.dbo.careerclust

--	11	CareerPaths	L	
insert into CareerPaths
select '22', * from wid28.dbo.careerpaths


--	13	BLSEducation	L	
insert into BLSEducation
select * from wid28.dbo.education

--	14	Experience	L	
insert into Experience
select * from wid28.dbo.experience

--	15	BLSTrainingCodes	L	
insert into BLSTrainingCodes
select * from wid28.dbo.training

--	16	CPIItems	L	
-- from BLS
insert into CPIItems
values ('AA0','All items - old base'),
('AA0R','Purchasing power of the consumer dollar - old base'),
('SA0','All items'),
('SA0E','Energy'),
('SA0L1','All items less food'),
('SA0L12','All items less food and shelter'),
('SA0L12E','All items less food, shelter, and energy'),
('SA0L12E4','All items less food, shelter, energy, and used cars and trucks'),
('SA0L1E','All items less food and energy'),
('SA0L2','All items less shelter'),
('SA0L5','All items less medical care'),
('SA0LE','All items less energy'),
('SA0R','Purchasing power of the consumer dollar'),
('SA311','Apparel less footwear'),
('SAA','Apparel'),
('SAA1','Men''s and boys'' apparel'),
('SAA2','Women''s and girls'' apparel'),
('SAC','Commodities'),
('SACE','Energy commodities'),
('SACL1','Commodities less food'),
('SACL11','Commodities less food and beverages'),
('SACL1E','Commodities less food and energy commodities'),
('SACL1E4','Commodities less food, energy, and used cars and trucks'),
('SAD','Durables'),
('SAE','Education and communication'),
('SAE1','Education'),
('SAE2','Communication'),
('SAE21','Information and information processing'),
('SAEC','Education and communication commodities'),
('SAES','Education and communication services'),
('SAF','Food and beverages'),
('SAF1','Food'),
('SAF11','Food at home'),
('SAF111','Cereals and bakery products'),
('SAF112','Meats, poultry, fish, and eggs'),
('SAF1121','Meats, poultry, and fish'),
('SAF11211','Meats'),
('SAF113','Fruits and vegetables'),
('SAF1131','Fresh fruits and vegetables'),
('SAF114','Nonalcoholic beverages and beverage materials'),
('SAF115','Other food at home'),
('SAF116','Alcoholic beverages'),
('SAG','Other goods and services'),
('SAG1','Personal care'),
('SAGC','Other goods'),
('SAGS','Other personal services'),
('SAH','Housing'),
('SAH1','Shelter'),
('SAH2','Fuels and utilities'),
('SAH21','Household energy'),
('SAH3','Household furnishings and operations'),
('SAH31','Household furnishings and supplies'),
('SAM','Medical care'),
('SAM1','Medical care commodities'),
('SAM2','Medical care services'),
('SAN','Nondurables'),
('SAN1D','Domestically produced farm food'),
('SANL1','Nondurables less food'),
('SANL11','Nondurables less food and beverages'),
('SANL113','Nondurables less food, beverages, and apparel'),
('SANL13','Nondurables less food and apparel'),
('SAR','Recreation'),
('SARC','Recreation commodities'),
('SARS','Recreation services'),
('SAS','Services'),
('SAS24','Utilities and public transportation'),
('SAS2RS','Rent of shelter'),
('SAS367','Other services'),
('SAS4','Transportation services'),
('SASL2RS','Services less rent of shelter'),
('SASL5','Services less medical care services'),
('SASLE','Services less energy services'),
('SAT','Transportation'),
('SAT1','Private transportation'),
('SATCLTB','Transportation commodities less motor fuel'),
('SEAA','Men''s apparel'),
('SEAA01','Men''s suits, sport coats, and outerwear'),
('SEAA02','Men''s underwear, nightwear, swimwear and accessories'),
('SEAA03','Men''s shirts and sweaters'),
('SEAA04','Men''s pants and shorts'),
('SEAB','Boys'' apparel'),
('SEAC','Women''s apparel'),
('SEAC01','Women''s outerwear'),
('SEAC02','Women''s dresses'),
('SEAC03','Women''s suits and separates'),
('SEAC04','Women''s underwear, nightwear, swimwear and accessories'),
('SEAD','Girls'' apparel'),
('SEAE','Footwear'),
('SEAE01','Men''s footwear'),
('SEAE02','Boys'' and girls'' footwear'),
('SEAE03','Women''s footwear'),
('SEAF','Infants'' and toddlers'' apparel'),
('SEAG','Jewelry and watches'),
('SEAG01','Watches'),
('SEAG02','Jewelry'),
('SEEA','Educational books and supplies'),
('SEEB','Tuition, other school fees, and childcare'),
('SEEB01','College tuition and fees'),
('SEEB02','Elementary and high school tuition and fees'),
('SEEB03','Day care and preschool'),
('SEEB04','Technical and business school tuition and fees'),
('SEEC','Postage and delivery services'),
('SEEC01','Postage'),
('SEEC02','Delivery services'),
('SEED','Telephone services'),
('SEED03','Wireless telephone services'),
('SEED04','Residential telephone services'),
('SEEE','Information technology, hardware and services'),
('SEEE01','Computers, peripherals, and smart home assistants'),
('SEEE02','Computer software and accessories'),
('SEEE03','Internet services and electronic information providers'),
('SEEE04','Telephone hardware, calculators, and other consumer information items'),
('SEEEC','Information technology commodities'),
('SEFA','Cereals and cereal products'),
('SEFA01','Flour and prepared flour mixes'),
('SEFA02','Breakfast cereal'),
('SEFA03','Rice, pasta, cornmeal'),
('SEFB','Bakery products'),
('SEFB01','Bread'),
('SEFB02','Fresh biscuits, rolls, muffins'),
('SEFB03','Cakes, cupcakes, and cookies'),
('SEFB04','Other bakery products'),
('SEFC','Beef and veal'),
('SEFC01','Uncooked ground beef'),
('SEFC02','Uncooked beef roasts'),
('SEFC03','Uncooked beef steaks'),
('SEFC04','Uncooked other beef and veal'),
('SEFD','Pork'),
('SEFD01','Bacon, breakfast sausage, and related products'),
('SEFD02','Ham'),
('SEFD03','Pork chops'),
('SEFD04','Other pork including roasts, steaks, and ribs'),
('SEFE','Other meats'),
('SEFF','Poultry'),
('SEFF01','Chicken'),
('SEFF02','Other uncooked poultry including turkey'),
('SEFG','Fish and seafood'),
('SEFG01','Fresh fish and seafood'),
('SEFG02','Processed fish and seafood'),
('SEFH','Eggs'),
('SEFJ','Dairy and related products'),
('SEFJ01','Milk'),
('SEFJ02','Cheese and related products'),
('SEFJ03','Ice cream and related products'),
('SEFJ04','Other dairy and related products'),
('SEFK','Fresh fruits'),
('SEFK01','Apples'),
('SEFK02','Bananas'),
('SEFK03','Citrus fruits'),
('SEFK04','Other fresh fruits'),
('SEFL','Fresh vegetables'),
('SEFL01','Potatoes'),
('SEFL02','Lettuce'),
('SEFL03','Tomatoes'),
('SEFL04','Other fresh vegetables'),
('SEFM','Processed fruits and vegetables'),
('SEFM01','Canned fruits and vegetables'),
('SEFM02','Frozen fruits and vegetables'),
('SEFM03','Other processed fruits and vegetables including dried'),
('SEFN','Juices and nonalcoholic drinks'),
('SEFN01','Carbonated drinks'),
('SEFN02','Frozen noncarbonated juices and drinks'),
('SEFN03','Nonfrozen noncarbonated juices and drinks'),
('SEFP','Beverage materials including coffee and tea'),
('SEFP01','Coffee'),
('SEFP02','Other beverage materials including tea'),
('SEFR','Sugar and sweets'),
('SEFR01','Sugar and sugar substitutes'),
('SEFR02','Candy and chewing gum'),
('SEFR03','Other sweets'),
('SEFS','Fats and oils'),
('SEFS01','Butter and margarine'),
('SEFS02','Salad dressing'),
('SEFS03','Other fats and oils including peanut butter'),
('SEFT','Other foods'),
('SEFT01','Soups'),
('SEFT02','Frozen and freeze dried prepared foods'),
('SEFT03','Snacks'),
('SEFT04','Spices, seasonings, condiments, sauces'),
('SEFT05','Baby food and formula'),
('SEFT06','Other miscellaneous foods'),
('SEFV','Food away from home'),
('SEFV01','Full service meals and snacks'),
('SEFV02','Limited service meals and snacks'),
('SEFV03','Food at employee sites and schools'),
('SEFV04','Food from vending machines and mobile vendors'),
('SEFV05','Other food away from home'),
('SEFW','Alcoholic beverages at home'),
('SEFW01','Beer, ale, and other malt beverages at home'),
('SEFW02','Distilled spirits at home'),
('SEFW03','Wine at home'),
('SEFX','Alcoholic beverages away from home'),
('SEGA','Tobacco and smoking products'),
('SEGA01','Cigarettes'),
('SEGA02','Tobacco products other than cigarettes'),
('SEGB','Personal care products'),
('SEGB01','Hair, dental, shaving, and miscellaneous personal care products'),
('SEGB02','Cosmetics, perfume, bath, nail preparations and implements'),
('SEGC','Personal care services'),
('SEGC01','Haircuts and other personal care services'),
('SEGD','Miscellaneous personal services'),
('SEGD01','Legal services'),
('SEGD02','Funeral expenses'),
('SEGD03','Laundry and dry cleaning services'),
('SEGD04','Apparel services other than laundry and dry cleaning'),
('SEGD05','Financial services'),
('SEGE','Miscellaneous personal goods'),
('SEHA','Rent of primary residence'),
('SEHB','Lodging away from home'),
('SEHB01','Housing at school, excluding board'),
('SEHB02','Other lodging away from home including hotels and motels'),
('SEHC','Owners'' equivalent rent of residences'),
('SEHC01','Owners'' equivalent rent of primary residence'),
('SEHD','Tenants'' and household insurance'),
('SEHE','Fuel oil and other fuels'),
('SEHE01','Fuel oil'),
('SEHE02','Propane, kerosene, and firewood'),
('SEHF','Energy services'),
('SEHF01','Electricity'),
('SEHF02','Utility (piped) gas service'),
('SEHG','Water and sewer and trash collection services'),
('SEHG01','Water and sewerage maintenance'),
('SEHG02','Garbage and trash collection'),
('SEHH','Window and floor coverings and other linens'),
('SEHH01','Floor coverings'),
('SEHH02','Window coverings'),
('SEHH03','Other linens'),
('SEHJ','Furniture and bedding'),
('SEHJ01','Bedroom furniture'),
('SEHJ02','Living room, kitchen, and dining room furniture'),
('SEHJ03','Other furniture'),
('SEHK','Appliances'),
('SEHK01','Major appliances'),
('SEHK02','Other appliances'),
('SEHL','Other household equipment and furnishings'),
('SEHL01','Clocks, lamps, and decorator items'),
('SEHL02','Indoor plants and flowers'),
('SEHL03','Dishes and flatware'),
('SEHL04','Nonelectric cookware and tableware'),
('SEHM','Tools, hardware, outdoor equipment and supplies'),
('SEHM01','Tools, hardware and supplies'),
('SEHM02','Outdoor equipment and supplies'),
('SEHN','Housekeeping supplies'),
('SEHN01','Household cleaning products'),
('SEHN02','Household paper products'),
('SEHN03','Miscellaneous household products'),
('SEHP','Household operations'),
('SEHP01','Domestic services'),
('SEHP02','Gardening and lawncare services'),
('SEHP03','Moving, storage, freight expense'),
('SEHP04','Repair of household items'),
('SEMC','Professional services'),
('SEMC01','Physicians'' services'),
('SEMC02','Dental services'),
('SEMC03','Eyeglasses and eye care'),
('SEMC04','Services by other medical professionals'),
('SEMD','Hospital and related services'),
('SEMD01','Hospital services'),
('SEMD02','Nursing homes and adult day services'),
('SEMD03','Care of invalids and elderly at home'),
('SEME','Health insurance'),
('SEMF','Medicinal drugs'),
('SEMF01','Prescription drugs'),
('SEMF02','Nonprescription drugs'),
('SEMG','Medical equipment and supplies'),
('SERA','Video and audio'),
('SERA01','Televisions'),
('SERA02','Cable, satellite, and live streaming television service'),
('SERA03','Other video equipment'),
('SERA04','Purchase, subscription, and rental of video'),
('SERA05','Audio equipment'),
('SERA06','Recorded music and music subscriptions'),
('SERAC','Video and audio products'),
('SERAS','Video and audio services'),
('SERB','Pets, pet products and services'),
('SERB01','Pets and pet products'),
('SERB02','Pet services including veterinary'),
('SERC','Sporting goods'),
('SERC01','Sports vehicles including bicycles'),
('SERC02','Sports equipment'),
('SERD','Photography'),
('SERD01','Photographic equipment and supplies'),
('SERD02','Photographers and photo processing'),
('SERE','Other recreational goods'),
('SERE01','Toys'),
('SERE02','Sewing machines, fabric and supplies'),
('SERE03','Music instruments and accessories'),
('SERF','Other recreation services'),
('SERF01','Club membership for shopping clubs, fraternal, or other organizations, or participant sports fees'),
('SERF02','Admissions'),
('SERF03','Fees for lessons or instructions'),
('SERG','Recreational reading materials'),
('SERG01','Newspapers and magazines'),
('SERG02','Recreational books'),
('SETA','New and used motor vehicles'),
('SETA01','New vehicles'),
('SETA02','Used cars and trucks'),
('SETA03','Leased cars and trucks'),
('SETA04','Car and truck rental'),
('SETB','Motor fuel'),
('SETB01','Gasoline (all types)'),
('SETB02','Other motor fuels'),
('SETC','Motor vehicle parts and equipment'),
('SETC01','Tires'),
('SETC02','Vehicle accessories other than tires'),
('SETD','Motor vehicle maintenance and repair'),
('SETD01','Motor vehicle body work'),
('SETD02','Motor vehicle maintenance and servicing'),
('SETD03','Motor vehicle repair'),
('SETE','Motor vehicle insurance'),
('SETF','Motor vehicle fees'),
('SETF01','State motor vehicle registration and license fees'),
('SETF03','Parking and other fees'),
('SETG','Public transportation'),
('SETG01','Airline fares'),
('SETG02','Other intercity transportation'),
('SETG03','Intracity transportation'),
('SS01031','Rice'),
('SS02011','White bread'),
('SS02021','Bread other than white'),
('SS02041','Fresh cakes and cupcakes'),
('SS02042','Cookies'),
('SS02063','Fresh sweetrolls, coffeecakes, doughnuts'),
('SS0206A','Crackers, bread, and cracker products'),
('SS0206B','Frozen and refrigerated bakery products, pies, tarts, turnovers'),
('SS04011','Bacon and related products'),
('SS04012','Breakfast sausage and related products'),
('SS04031','Ham, excluding canned'),
('SS05011','Frankfurters'),
('SS05014','Lamb and organ meats'),
('SS05015','Lamb and mutton'),
('SS0501A','Lunchmeats'),
('SS06011','Fresh whole chicken'),
('SS06021','Fresh and frozen chicken parts'),
('SS07011','Shelf stable fish and seafood'),
('SS07021','Frozen fish and seafood'),
('SS09011','Fresh whole milk'),
('SS09021','Fresh milk other than whole'),
('SS10011','Butter'),
('SS11031','Oranges, including tangerines'),
('SS13031','Canned fruits'),
('SS14011','Frozen vegetables'),
('SS14021','Canned vegetables'),
('SS14022','Dried beans, peas, and lentils'),
('SS16011','Margarine'),
('SS16014','Peanut butter'),
('SS17031','Roasted coffee'),
('SS17032','Instant coffee'),
('SS18041','Salt and other seasonings and spices'),
('SS18042','Olives, pickles, relishes'),
('SS18043','Sauces and gravies'),
('SS1804B','Other condiments'),
('SS18064','Prepared salads'),
('SS20021','Whiskey at home'),
('SS20022','Distilled spirits, excluding whiskey, at home'),
('SS20051','Beer, ale, and other malt beverages away from home'),
('SS20052','Wine away from home'),
('SS20053','Distilled spirits away from home'),
('SS27051','Land-line interstate toll calls'),
('SS27061','Land-line intrastate toll calls'),
('SS30021','Laundry equipment'),
('SS31022','Video discs and other media'),
('SS31023','Video game hardware, software and accessories'),
('SS33032','Stationery, stationery supplies, gift wrap'),
('SS45011','New cars'),
('SS4501A','New cars and trucks'),
('SS45021','New trucks'),
('SS45031','New motorcycles'),
('SS47014','Gasoline, unleaded regular'),
('SS47015','Gasoline, unleaded midgrade'),
('SS47016','Gasoline, unleaded premium'),
('SS47021','Motor oil, coolant, and fluids'),
('SS48021','Vehicle parts and equipment other than tires'),
('SS52051','Parking fees and tolls'),
('SS53021','Intercity bus fare'),
('SS53022','Intercity train fare'),
('SS53023','Ship fare'),
('SS53031','Intracity mass transit'),
('SS5702','Inpatient hospital services'),
('SS5703','Outpatient hospital services'),
('SS61011','Toys, games, hobbies and playground equipment'),
('SS61021','Film and photographic supplies'),
('SS61023','Photographic equipment'),
('SS61031','Pet food'),
('SS61032','Purchase of pets, pet supplies, accessories'),
('SS62011','Automobile service clubs'),
('SS62031','Admission to movies, theaters, and concerts'),
('SS62032','Admission to sporting events'),
('SS62051','Photographer fees'),
('SS62052','Photo Processing'),
('SS62053','Pet services'),
('SS62054','Veterinarian services'),
('SS62055','Subscription and rental of video and video games'),
('SS68021','Checking account and other bank services'),
('SS68023','Tax return preparation and other accounting fees'),
('SSEA011','College textbooks'),
('SSEE041','Smartphones'),
('SSFV031A','Food at elementary and secondary schools'),
('SSGE013','Infants'' equipment'),
('SSHJ031','Infants'' furniture')

--	17	OccSubLevels	L	
insert into OccSubLevels
select subtot, subtotdesc from wid28.dbo.occsub

--	18	OccDirectories	L	
insert into OccDirectories
select matoccode, estyear, periodtype, estperiod, projyear, case when matocctitl is null then '' else matocctitl end, subtot 
from wid28.dbo.occdir o
left join wid28.dbo.periodid p
on o.periodid = p.periodid


--	19	IndCodeTypes	L	
insert into IndCodeTypes
select * from wid28.dbo.indtypes

insert into IndCodeTypes
values ('06', 'CES Series Code'),
('07', 'JOLTS Code')

--	20	WageRateTypes	L	
insert into WageRateTypes
select * from wid28.dbo.ratetype

--	21	SalesTypes	L	
insert into SalesTypes
select * from wid28.dbo.salestyp

--	22	WageSources	L	
insert into WageSources
select * from wid28.dbo.wgsource where wagesource not in ('6','9') --remove state-defined placeholders

--	23	IndSubLevels	L	
insert into IndSubLevels
select distinct subtot, subtotdesc from wid28.dbo.indsub

--	24	Ownerships	L	
insert into Ownerships
select distinct ownership, ownertitle from wid28.dbo.ownershp


--	25	PopulationSources	L	
insert into PopulationSources
select * from wid28.dbo.popsource where popsource not in ('6','9') --remove state-defined placeholders


--	26	UnitTypes	L	
insert into UnitTypes
select * from wid28.dbo.unittype where unittype not in ('50','70') --remove state-defined placeholders


--	27	CESCodes	L	
insert into CESCodes
select distinct '06', seriescode, seriesttls, seriesttll, seriesdesc, serieslvl from wid28.dbo.cescode


--	28	CIPCodes	L	
insert into CIPCodes
select cipcodetype, cipcode,  left(ciptitle,100), ciptitle,cipdesc, ciplevel from wid28.dbo.cipcode


--	30	CompleterTypes	L	
insert into CompleterTypes
select * from wid28.dbo.compltyp

--	31	Genders	L	
insert into Genders
select * from wid28.dbo.gender

--	32	InstitutionOwnerships	L	
insert into InstitutionOwnerships
select * from wid28.dbo.instown

--	33	InstitutionTypes	L	
insert into InstitutionTypes
select * from wid28.dbo.insttype

--	34	LengthTypes	L	
insert into LengthTypes
select * from wid28.dbo.lentype

--	35	NAICSDomains	L	
insert into NAICSDomains
select * from wid28.dbo.naicdom

--	36	NAICSSuperSectors	L	
insert into NAICSSuperSectors
select * from wid28.dbo.naicsupr

--	37	NAICSSectors	L	
insert into NAICSSectors
select * from wid28.dbo.naicsect

--	38	NAICSLevels	L	
insert into NAICSLevels
select * from wid28.dbo.naicslvl

--	39	NAICSCodes	L	
insert into NAICSCodes
select '10' as naicscodetype, naicscode, naicstitle, naicstitll, naicsdesc, naicslvl, naicsect, '0' as flag 
from wid28.dbo.naiccode

--	40	RaceCodes	L	
insert into RaceCodes
values ('00', 'All Races'),
	   ('01', 'White'),
	   ('02', 'Black'),
	   ('03', 'Asian'),
	   ('04', 'American Indian or Alaskan Native')

insert into EthnicityCodes
values ('0', 'All Ethnicities'),
	   ('1', 'Hispanic'),
	   ('2', 'Non-Hispanic')

--	41	ONETCodes	L	
insert into ONETCodes
select '21' as onetcodetype, onetcode, onetyear, onettitle, onetdesc 
from wid28.dbo.onetcode

--	42	IndDirectories	L	
insert into IndDirectories
select matincode, estyear, periodtype, estperiod, projyear, matintitle, subtot , ownership
from wid28.dbo.inddir o
left join wid28.dbo.periodid p
on o.periodid = p.periodid

--	43	TransferPaymentTypes	L	
--insert into TransferPaymentTypes


--	44	SOCCodes	L	
insert into SOCCodes
select soccodetype, soccode, soctitle, soctitlel, socdesc, education, experience, training, oesflag, socparent 
from wid28.dbo.soccode

--	45	StateProgramCode	L	
insert into StateProgramCode
select * from wid28.dbo.stprogcd

--	46	OccupationCodes	L	
insert into OccupationCodes
select * from wid28.dbo.occcodes
where codetitle is not null

--	47	IndustryCodes	L	
insert into IndustryCodes
select * from wid28.dbo.indcodes

--	48	TaxType	L	
insert into TaxTypes
select * from wid28.dbo.taxtype

--	49	AgeGroupTypes	L	
insert into AgeGroupTypes
values ('01','Current Population Survey'),
('02','Census Population')


--	51	AgeGroups	L	
insert into AgeGroups (agegroup, agegrouptype, agegroupdesc)
values ('01','01','16 and over'),
('02','01','16 to 19'),
('03','01','20 and over'),
('04','01','20 to 24'),
('05','01','25 to 34'),
('06','01','35 to 44'),
('07','01','45 to 54'),
('08','01','55 to 64'),
('09','01','65 and over'),
('10','02','Less than 5 years'),
('11','02','5 to 9 years'),
('12','02','10 to 14 years'),
('13','02','15 to 19 years'),
('14','02','20 to 24 years'),
('15','02','25 to 29 years'),
('16','02','30 to 34 years'),
('17','02','35 to 44 years'),
('18','02','45 to 54 years'),
('19','02','55 to 59 years'),
('20','02','60 to 64 years'),
('21','02','65 to 69 years'),
('22','02','70 to 74 years'),
('23','02','75 to 79 years'),
('24','02','80 to 84 years'),
('25','02','85 years and over')

--	52	AnnualSalesCodes	L	
insert into AnnualSalesCodes
select * from wid28.dbo.annslflg

--	53	AnnualSalesRanges	L	
insert into AnnualSalesRanges
select * from wid28.dbo.annslrng

--	54	Benchmark	L	
insert into Benchmark
select * from wid28.dbo.benmark

--	55	CPITypes	L	
insert into CPITypes
select cpitype, cpititle, cpidesc from wid28.dbo.cpitype

--	56	CPISources	L	
insert into CPISources
select * from wid28.dbo.cpisource where cpisource not in ('6','8') --remove state-defined placeholders

--	57	CreditCodes	L	
insert into CreditCodes
select * from wid28.dbo.creditcd

--	58	EmpSizeFlag	L	
insert into EmpSizeFlag
select * from wid28.dbo.empszflg

--	59	EmpSizeRange	L	
insert into EmpSizeRange
select * from wid28.dbo.empszrng

--	60	GrowthCodes	L	
insert into GrowthCodes
select * from wid28.dbo.growcode where growcode not in ('50','70') --remove state-defined placeholders

--	61	IncomeSources	L	
insert into IncomeSources
select * from wid28.dbo.incsourc where incsource not in ('6','8') --remove state-defined placeholders

--	62	IncomeTypes	L	
insert into IncomeTypes
select * from wid28.dbo.incomtyp where inctype not in ('50','70') --remove state-defined placeholders

--	63	LayTitles	L	
insert into LayTitles
select * from wid28.dbo.laytitle

--	64	LicenseNumberTypes	L	
insert into LicenseNumberTypes
select * from wid28.dbo.licnumty where licnumtype not in ('50','70') --remove state-defined placeholders

--	65	LocationStatuses	L	
insert into LocationStatuses
select * from wid28.dbo.locstat

--	66	PrivateGovt	L	
insert into PrivateGovt
select * from wid28.dbo.prvgovst

--	67	BEDTypes	L	
insert into BEDTypes
select * from wid28.dbo.bedtypes

--	68	ClassTime	L	
insert into ClassTime
select * from wid28.dbo.classtime

--	69	ContactProTitles	L	
insert into ContactProTitles
select * from wid28.dbo.contactpro

--	70	ContactTitles	L	
insert into ContactTitles
select * from wid28.dbo.contacttitle

--	71	SpecialIDs	L	
insert into SpecialIDs
values ('GRN', 'Green'),
('GTH', 'High Growth'),
('RMT', 'Often Remote')

--	72	StockExchange	L	
insert into StockExchange
select * from wid28.dbo.stockexch

--	73	IndOccSpecialIDs	L	
--NOTE: this makes some assumptions about the data that's already in the table
insert into IndOccSpecialIDs
select stfips, '01', '0000', '000000', '2020', indcodty, indcode, occodetype, occcode, 'GRN', pctgreen from wid28.dbo.iospecialid

--	74	BED	D	
insert into BED
select stfips, 	   
case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion, 
		area, periodyear, periodtype, period, indcodtype, indcode, adjusted, bedtypcode, bedemploy, bedempperc, bedestabs, bedestperc, suppress 
from wid28.dbo.bed

--	75	BuildingPermits	D	
insert into BuildingPermits
select stfips, 	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion, 
		area, periodyear, periodtype, period, unittype, units, unitcost 
from wid28.dbo.blding

--	76	CES	D	
insert into CES
select top 1 stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, '06' as seriescodetype, seriescode, adjusted, benchmark, prelim, empces, empprodwrk, 
		empfemale, hours, earnings, hourearn, supprecord, supphe, supppw, suppfem, hoursallwrkr, earningsallwrkr, hourearnallwrkr, suppheallwrkr
from wid28.dbo.ces


--	77	Commute	D	
insert into Commute
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, wkstfips, 
	   case when wkareaty = '40' then '41'
			when wkareaty = '21' then '31'
			when wkareaty = '22' then '32'
			when wkareaty = '23' then '33'
			when wkareaty = '24' then '34'
			else wkareaty
			end as wkareaty,
	   case when wkareaty = '40' then '2010'
			when wkareaty = '21' then '2010'
			when wkareaty = '22' then '2010'
			when wkareaty = '23' then '2010'
			when wkareaty = '24' then '2010'
			else '0000'
			end as wkareatyversion,
		wkarea, workers
from wid28.dbo.commute
where areatype='04'


--	78	CPI	D	
insert into CPI
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, cpitype, cpisource, cpi, pctchgy, pctchgm 
from wid28.dbo.cpi


--	79	CPIPlus	D	
insert into CPIPlus
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, adjusted, cpiindex, cpiitem, cpisource, basis, cpi, pctchangey, pctchangem 
from wid28.dbo.cpiplus 
where areatype in ('00','41')




--	80	Demographics	D	
insert into Demographics
SELECT stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period
      ,[popsource]
      ,[population]
      ,[female]
      ,[male]
      ,[median]
      ,[medianmale]
      ,[medianfem]
      ,[totunder5]
      ,[femunder5]
      ,[maleunder5]
      ,[tot5to9]
      ,[fem5to9]
      ,[male5to9]
      ,[tot10to14]
      ,[fem10to14]
      ,[male10to14]
      ,[tot15to19]
      ,[fem15to19]
      ,[male15to19]
      ,[tot15to17]
      ,[fem15to17]
      ,[male15to17]
      ,[tot18to19]
      ,[fem18to19]
      ,[male18to19]
      ,[tot20to24]
      ,[fem20to24]
      ,[male20to24]
      ,[tot20]
      ,[fem20]
      ,[male20]
      ,[tot21]
      ,[fem21]
      ,[male21]
      ,[tot22to24]
      ,[fem22to24]
      ,[male22to24]
      ,[tot25to34]
      ,[fem25to34]
      ,[male25to34]
      ,[tot25to29]
      ,[fem25to29]
      ,[male25to29]
      ,[tot30to34]
      ,[fem30to34]
      ,[male30to34]
      ,[tot35to44]
      ,[fem35to44]
      ,[male35to44]
      ,[tot35to39]
      ,[fem35to39]
      ,[male35to39]
      ,[tot40to44]
      ,[fem40to44]
      ,[male40to44]
      ,[tot45to54]
      ,[fem45to54]
      ,[male45to54]
      ,[tot45to49]
      ,[fem45to49]
      ,[male45to49]
      ,[tot50to54]
      ,[fem50to54]
      ,[male50to54]
      ,[tot55to59]
      ,[fem55to59]
      ,[male55to59]
      ,[tot60to64]
      ,[fem60to64]
      ,[male60to64]
      ,[tot60to61]
      ,[fem60to61]
      ,[male60to61]
      ,[tot62to64]
      ,[fem62to64]
      ,[male62to64]
      ,[tot65to69]
      ,[fem65to69]
      ,[male65to69]
      ,[tot65to66]
      ,[fem65to66]
      ,[male65to66]
      ,[tot67to69]
      ,[fem67to69]
      ,[male67to69]
      ,[tot70to74]
      ,[fem70to74]
      ,[male70to74]
      ,[tot75to84]
      ,[fem75to84]
      ,[male75to84]
      ,[tot75to79]
      ,[fem75to79]
      ,[male75to79]
      ,[tot80to84]
      ,[fem80to84]
      ,[male80to84]
      ,[tot85xx]
      ,[fem85xx]
      ,[male85xx]
      ,[tot18xx]
      ,[fem18xx]
      ,[male18xx]
      ,[tot21xx]
      ,[fem21xx]
      ,[male21xx]
      ,[tot62xx]
      ,[fem62xx]
      ,[male62xx]
      ,[onerace]
      ,[white]
      ,[black]
      ,[naan]
      ,[asian]
      ,[pacisland]
      ,[other]
      ,[twomoraces]
      ,[hispanic]
      ,[hispwhite]
      ,[hispblack]
      ,[hispnaan]
      ,[hispasian]
      ,[hisppacisl]
      ,[hispother]
      ,[hisp2race]
  FROM wid28.[dbo].[demographics]
  where areatype in ('00','01','32','31','04')

--	81	EmpDBInf	L	
insert into EmpDBInf
select * from wid28.dbo.empdbinf

--	82	EmpDB	D	
insert into EmpDB
SELECT  stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion
      ,area
	  ,[uniqueid]
      ,[fein]
      ,[lastupdate]
      ,[name]
      ,[addressp]
      ,[cityp]
      ,[statep]
      ,[zipcodep]
      ,[zipplusp]
      ,[latitude]
      ,[longitude]
      ,[geopcode]
      ,[censustract]
      ,[censusblockgrp]
      ,[addressm]
      ,[citym]
      ,[statem]
      ,[zipcodem]
      ,[zipplusm]
      ,[addressl]
      ,[cityl]
      ,[statel]
      ,[zipcodel]
      ,[zipplusl]
      ,[telenum]
      ,[cntctlname]
      ,[cntctfname]
      ,[cntcttitle]
      ,[contacttitlecode]
      ,[contactprotitle]
      ,[contactgender]
      ,[contactemail]
      ,[tollfreetele]
      ,[faxnumber]
      ,[weburl]
      ,[busdesc]
      ,[primarysic]
      ,[sic2]
      ,[sic3]
      ,[sic4]
      ,[sic5]
      ,[primnaics]
      ,[naics2]
      ,[naics3]
      ,[naics4]
      ,[naics5]
      ,[privgovsta]
      ,[locstat]
      ,[stockexchcode]
      ,[stockticker]
      ,[whitecollarpct]
      ,[whitecollarind]
      ,[empsizrng]
      ,[empsizval]
      ,[empsizflg]
      ,[annsalrng]
      ,[annsalval]
      ,[annsalflg]
      ,[yearest]
      ,[creditcd]
      ,[hdqtrsid]
      ,[parentid]
      ,[ultimateparentid]
      ,[foreignparentind]
      ,[exportimportind]
      ,[businesstype]
      ,[workathome]
      ,[releaseno]
  FROM wid28.[dbo].[empdb]


--	83	Income	D	
insert into Income
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, inctype, incsource, income, incrank, population, releasedate 
from wid28.dbo.income
where areatype in ('00','01','04','31','32')

--	84	JOLTSTypes	L	
insert into JOLTSTypes
values ('HI','Hires'),
('JO','Job openings'),
('LD','Layoffs and discharges'),
('OS','Other Separation'),
('QU','Quits'),
('TS','Total Separations')

--	85	IndustrySums	A	
insert into IndustrySums
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, indcodetyp, indcode, indsource, employers 
from wid28.dbo.indsum

--	86	JOLTS	D	
--NEW

--	87	Industry	D	
insert into Industry
SELECT stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area
      ,[periodyear]
      ,[periodtype]
      ,[period]
      ,[indcodty]
      ,[indcode]
      ,[ownership]
      ,[prelim]
      ,[firms]
      ,[estab]
      ,[avgemp]
      ,[mnth1emp]
      ,[mnth2emp]
      ,[mnth3emp]
      ,[topempav]
      ,[totwage]
      ,[avgwkwage]
      ,[taxwage]
      ,[contrib]
      ,[suppress]
  FROM wid28.[dbo].[industry]

--	88	ProjectionsMatrix	D	
insert into ProjectionsMatrix

SELECT o.stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area
      ,estyear, periodtype, estperiod
      ,[matincode]
      ,[matoccode]
	  , projyear
      ,[estemp]
      ,[projemp]
      ,[pctestind]
      ,[pctestocc]
      ,[pctprojind]
      ,[pctprojocc]
      ,[nchg]
      ,[pchg]
      ,[growrate]
      ,[growcode]
      --,[aopeng]
      --,[aopenr]
      --,[aopent]
      ,[exits]
      ,[annualexits]
      ,[transfers]
      ,[annualtransfers]
      ,[change]
      ,[annualchange]
      ,[openings]
      ,[annualopenings]
      ,case when [suppress] is null then '0' else suppress end 
  FROM wid28.[dbo].[iomatrix] o
left join wid28.dbo.periodid p
on o.periodid = p.periodid


--	89	LaborForce	D	
insert into LaborForce
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, adjusted, prelim, benchmark, laborforce, emplab, unemp, unemprate, clfprate, emppopratio 
from wid28.dbo.labforce

--	90	LicenseTypes	L	
insert into LicenseTypes
select * from wid28.dbo.licensetypes

--	91	LicenseExams	L	
insert into LicenseExams
select * from wid28.dbo.licenseexams

--	92	LicenseEducation	L	
insert into LicenseEducation
select * from wid28.dbo.licenseeducation

--	93	LicenseContinuingEdu	L	
insert into LicenseContinuingEdu
select * from wid28.dbo.liccontinuingedu

--	94	LicenseCertifications	L	
insert into LicenseCertifications
select * from wid28.dbo.licensecertification

--	95	LicenseExperience	L	
insert into LicenseExperience
select * from wid28.dbo.licenseexperience

--	96	LicenseCriminal	L	
insert into LicenseCriminal
select * from wid28.dbo.licensecriminal

--	97	LicensePhysicalReqs	L	
insert into LicensePhysicalReqs
select * from wid28.dbo.licensephysicalreqs

--	98	LicenseActiveStatuses	L	
insert into LicenseActiveStatuses
select * from wid28.dbo.licenseactivestatus

--	99	LicenseVeteran	L	
insert into LicenseVeteran
select licveteran, licveterandesc from wid28.dbo.licenseveteran 


--	100	LicenseAuthorities	D	
insert into LicenseAuthorities
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, licauthid, department, division, board, address1, address2, 
		case when city is null then '' else city end, 
		case when st is null then '' else st end,
		case when zip is null then '' else zip end, 
		zipext, latitude, longitude, geopcode, telephone, teleext, fax, contact, email, url
from wid28.dbo.licauth
where areatype<>'20'

--	101	License	D	
insert into License
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, licenseid, licauthid, lictitle, licdesc, licensetype, exam, education, continuingedu, certification, experience, criminal, physical, veteran, inactive, licenseurl, licenseupdated 
from wid28.dbo.license
where areatype<>'20'


--	102	LicenseXLicense	X	
--NEW

--	103	LicenseHistory	D	
insert into LicenseHistory
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, licenseid, licnumtype, licnum 
from wid28.dbo.lichist

--	104	IOWage	D	
insert into IOWage
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, indcodty, indcode, occodetype, occcode, wagesource, empcount,ratetype,  response, mean, 
		entrywg, experience, pct10, pct25, median, pct75, pct90, 
		udpct, upctwage, udrnglopct, udrnghipct, udrngmean, wpctrelerr, epctrelerr, panelcode, suppresswage,suppressall, suppressemp
from wid28.dbo.iowage





--	105	TransferPayments	D	
--NEW

--	106	Population	D	
insert into Population
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, popsource, population, releasedate 
from wid28.dbo.populatn


--	107	Schools	D	
insert into Schools
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, instcode, insttype, instown, instname1, instname2, address1, address2, city, st, zip, zipext, 
		latitude, longitude, geopcode, telephone, teleext, fax, url, contact, distancelearn, satellitecampus
from wid28.dbo.schools


--	108	ProgramCompleters	D	
insert into ProgramCompleters
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, instcode, periodyear, periodtype, period, codetype, code, compltype, completers, placecomp
from wid28.dbo.progcomp

insert into occupationcodes select * from wid28.dbo.occcodes where codetype='15'

--	109	Programs	D	
insert into Programs
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, instcode, codetype, code, compltype, length, lengthtype, progcost, progtitle, progdesc, cipcodetype, cipcode, url, classroom, online, classtime, etplapproval
from wid28.dbo.programs

insert into occupationcodes select * from wid28.dbo.occcodes where codetype='03'
insert into occupationcodes select * from wid28.dbo.occcodes where codetype='20'

--	110	SalesRevenue	D	
insert into SalesRevenue
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, salestype, sales 
from wid28.dbo.sales

--	111	Supply	D	
insert into Supply

select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, insttype, instown, codetype, code, compltype, completers 
from wid27.dbo.supply 

select * from wid27.dbo.cipcode where cipcodetype='20' and cipcodetype+cipcode not in (select cipcodetype+cipcode from cipcodes)
insert into occupationcodes
select distinct stfips, cipcodetype, cipcode, ciptitle from cipcodes cross join statefips where stfips<>'00' and cipcodetype='20'
select * from occupationcodes where codetype = '15'

insert into occupationcodes
select distinct stfips, '20', '490109', 'Remote Aircraft Pilot' from cipcodes cross join statefips where cipcodetype='20'

select * from wid27.dbo.occcodes where code='490109    '
--	112	TaxRevenues	D	
insert into TaxRevenues
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, taxtype, taxrevenue 
from wid28.dbo.tax

--	113	UIClaims	D	
insert into UIClaims
select stfips,
	   case when areatype = '40' then '41'
			when areatype = '21' then '31'
			when areatype = '22' then '32'
			when areatype = '23' then '33'
			when areatype = '24' then '34'
			else areatype
			end as areatype,
	   case when areatype = '40' then '2010'
			when areatype = '41' then '2018'
			when areatype = '21' then '2010'
			when areatype = '22' then '2010'
			when areatype = '23' then '2010'
			when areatype = '24' then '2010'
			when areatype = '31' then '2017'
			when areatype = '32' then '2017'
			when areatype = '33' then '2017'
			when areatype = '34' then '2017'
			else '0000'
			end as areatypeversion,
		area, periodyear, periodtype, period, claimtype, occodetype, occcode, indcodty, indcode, 
		agegroup, raceethn, raceethn, --these two will need to be mapped since they're being divided into two columns
		gender, claimants, weekscomp, firstpayments, duration 
from wid28.dbo.uiclaims


--	114	OccupationXOccupation	X	
insert into OccupationXOccupation
select * from wid28.dbo.occxocc

--	115	IndustryXIndustry	X	
insert into IndustryXIndustry
select * from wid28.dbo.indxind



--	118	LayTitleXOcc	X	
insert into LayTitleXOcc
select * from wid28.dbo.laytxocc

--	119	LicenseXOcc	X	
insert into LicenseXOcc
select * from wid28.dbo.licxocc

--	120	MatrixXInd	X	
insert into MatrixXInd
select o.stfips, matincode, estyear, '01', estperiod, projyear, indcodetype, indcode, subtot from wid28.dbo.matxind o
left join wid28.dbo.periodid p
on o.periodid = p.periodid



--	121	MatrixXOcc	X	
insert into MatrixXOcc
select o.stfips, matoccode, estyear, '01', estperiod, projyear, occodetype, occcode, subtot from wid28.dbo.matxocc o
left join wid28.dbo.periodid p
on o.periodid = p.periodid

--	122	TableList	A	
insert into TableList
select * from wid28.dbo.tabllist

--	123	TableSource	A	
insert into TableSource
select * from wid28.dbo.tablsrce