USE [WID30] GO ------------Projections------------------------------------------------------------------------ create view c_periodid as SELECT distinct stfips, right('00'+cast(row_number() over(order by periodyear, periodtype, period) as varchar),2) as periodid, periodtype, periodyear as estyear, period as estperiod, projectedyear as projyear, period as projperiod, case when stfips='00' and cast(projectedyear as int)-cast(periodyear as int) > 5 then 'National ' + periodyear + ' Long-term Projections' when cast(projectedyear as int)-cast(periodyear as int) > 5 then 'State ' + periodyear + ' Long-term Projections' when stfips = '00' then 'National ' + periodyear + ' Short-term Projections' else 'State ' + periodyear + ' Short-term Projections' end as perioddesc from projectionsmatrix; create view c_iomatrix as SELECT m.[StFips] as stfips ,[AreaType] as areatype ,[Area] as area ,periodid ,[MatrixIndCode] as matincode ,[MatrixOccCode] as matoccode ,[EstimatedEmp] as estemp ,[ProjectedEmp] as projemp ,[PctEstInd] as pctestind ,[PctEstOcc] as pctestocc ,[PctProjInd] as pctprojind ,[PctProjOcc] as pctprojocc ,[NumericChange] as nchg ,[PercentChange] as pchg ,[GrowthRate] as growrate ,[GrowthCode] as growcode ,[Exits] as exits ,[AnnualExits] as annualexits ,[Transfers] as transfers ,[AnnualTransfers] as annualtransfers ,[Change] as change ,[AnnualChange] as annualchange ,[Openings] as openings ,[AnnualOpenings] as annualopenings ,[Suppress] as suppress FROM [dbo].[ProjectionsMatrix] m left join c_periodid p on m.periodyear=p.estyear and m.periodtype=p.periodtype and m.period=p.estperiod and m.projectedyear=p.projyear; ------------Licenses------------------------------------------------------------------------ create view c_license as SELECT [StFips] as stfips ,[AreaType] as areatype ,[Area] as area ,[LicenseID] as licenseid ,[LicAuthID] as licauthid ,[LicenseTitle] as lictitle ,[LicenseDesc] as licdesc ,[LicenseType] as licensetype ,[Exam] as exam ,[Education] as education ,[ContinuingEdu] as continuingedu ,[Certification] as certification ,[Experience] as experience ,[Criminal] as criminal ,[PhysicalReq] as physical ,[Veteran] as veteran ,[Inactive] as inactive ,[LicenseURL] as licenseurl ,[LicenseUpdated] as licenseupdated FROM [dbo].[License]; create view c_licauth as SELECT [StFips] as stfips ,[AreaType] as areatype ,[Area] as area ,[LicAuthID] as licauthid ,[Department] as department ,[Division] as division ,[Board] as board ,[Address1] as address1 ,[Address2] as address2 ,[City] as city ,[State] as st ,[ZipCode] as zip ,[ZipExt] as zipext ,[Latitude] as latitude ,[Longitude] as longitude ,[GeoPrecisionCode] as geopcode ,[Telephone] as telephone ,[TeleExt] as teleext ,[Fax] as fax ,[Contact] as contact ,[Email] as email ,[URL] as url FROM [dbo].[LicenseAuthorities]; ------------CES------------------------------------------------------------------------ create view c_ces as SELECT [StFips] as stfips ,[AreaType] as areatype ,[Area] as area ,[PeriodYear] as periodyear ,[PeriodType] as preiodtype ,[Period] as period ,[SeriesCode] as seriescode ,[Adjusted] as adjusted ,[Benchmark] as benchmark ,[Prelim] as prelim ,[EmpCES] as empces ,[EmpProductionWorkers] as empprodwrk ,[EmpFemaleWorkers] as empfemale ,[HoursPerWeek] as hours ,[EarningsPerWeek] as earnings ,[EarningsPerHour] as hourearn ,[SuppRecord] as supprecord ,[SuppHoursEarnings] as supphe ,[SuppProdWorkers] as supppw ,[SuppFemaleWorkers] as suppfem ,[HoursAllWorkers] as hoursallwrkr ,[EarningsAllWorkers] as earningsallwrkr ,[HourlyEarningsAllWorkers] as hourearnallwrkr ,[SuppHEAllWrkr] as suppheallwrkr FROM [dbo].[CES]; create view c_cescode as SELECT '00' as stfips ,[SeriesCode] as seriescode ,[SeriesDesc] as seriesdesc ,[SeriesTitle] as seriesttls ,[SeriesTitleLong] as seriesttll ,[SeriesLevel] as serieslvl FROM [dbo].[CESCodes]; ------------LAUS------------------------------------------------------------------------ create view c_labforce as SELECT [StFips] as stfips ,[AreaType] as areatype ,[Area] as area ,[PeriodYear] as periodyear ,[PeriodType] as periodtype ,[Period] as period ,[Adjusted] as adjusted ,[Prelim] as prelim ,[Benchmark] as benchmark ,[LaborForce] as laborforce ,[Employed] as emplab ,[Unemployed] as unemp ,[UnemployedRate] as unemprate ,[CLFPRate] as clfprate ,[EmpPopRatio] as emppopratio FROM [dbo].[LaborForce] ------------OEWS------------------------------------------------------------------------ create view c_iowage as SELECT [StFips] as stfips ,[AreaType] as areatype ,[Area] as area ,[PeriodYear] as periodyear ,[PeriodType] as periodtype ,[Period] as period ,[IndCodeType] as indcodty ,[IndCode] as indcode ,[OccCodeType] as occodetype ,[OccCode] as occcode ,[WageSource] as wagesource ,[RateType] as ratetype ,[EmpCount] as empcount ,[ResponseRate] as response ,[MeanWage] as mean ,[EntryWage] as entrywg ,[ExperiencedWage] as experience ,[Percentile10Wage] as pct10 ,[Percentile25Wage] as pct25 ,[MedianWage] as median ,[Percentile75Wage] as pct75 ,[Percentile90Wage] as pct90 ,[UserDefinedPct] as udpct ,[UserDefinedPctWage] as upctwage ,[UserDefinedRangeLoPct] as udrnglopct ,[UserDefinedRangeHiPct] as udrnghipct ,[UserDefinedRangeMean] as udrngmean ,[WageRelativePctError] as wpctrelerr ,[EmpRelativePctError] as epctrelerr ,[PanelCode] as panelcode ,[SuppressAll] as suppressall ,[SuppressEmp] as suppressemp ,[SuppressWage] as suppresswage FROM [dbo].[IOWage] create view c_soccode as SELECT [SOCCode] as soccode ,[SOCCodeType] as soccodetype ,[SOCTitle] as soctitle ,[SOCTitleLong] as soctitlel ,[SOCDesc] as socdesc ,[Education] as education ,[Experience] as experience ,[Training] as training ,[Flag] as oesflag ,[SOCParent] as socparent ,null as oohtrntm FROM [dbo].[SOCCodes] ------------QCEW------------------------------------------------------------------------ create view c_industry as SELECT [StFips] as stfips ,[AreaType] as areatype ,[Area] as area ,[PeriodYear] as periodyear ,[PeriodType] as periodtype ,[Period] as period ,[IndCodeType] as indcodty ,[IndCode] as indcode ,[Ownership] as ownership ,[Prelim] as prelim ,[Firms] as firms ,[Establishments] as estab ,[QuarterAvgEmp] as avgemp ,[Month1Emp] as mnth1emp ,[Month2Emp] as mnth2emp ,[Month3Emp] as mnth3emp ,[TopEmployerAvgEmp] as topempav ,[TotalWages] as totwage ,[AvgWeeklyWage] as avgwkwage ,[TaxableWages] as taxwage ,[UIContributions] as contrib ,[Suppress] as suppress FROM [dbo].[Industry] create view c_naiccode as SELECT [NAICSCode] as naicscode ,[NAICSTitle] as naicstitle ,[NAICSTitleLong] as naicsttll ,[NAICSTitleDesc] as naicsdesc ,[NAICSLevel] as naicslvl ,[NAICSSector] as naicsect FROM [dbo].[NAICSCodes]