--Projections
create view a_Projections as
SELECT i.[StFips]
      ,i.[AreaType]
      ,i.[AreaTypeVersion]
      ,i.[Area]
	  ,[AreaName]
      ,i.[PeriodYear]
      ,i.[PeriodType]
      ,i.[Period]
	  --Customize for state projections periods
	  ,i.PeriodYear + ' - ' + i.ProjectedYear + ' ' + cast(cast(i.ProjectedYear as int)-cast(i.PeriodYear as int) as varchar) + '-year Projections' as PeriodDesc
      ,i.[MatrixIndCode]
	  ,[MatrixIndTitle]
      ,i.[MatrixOccCode]
	  ,[MatrixOccTitle]
      ,i.[ProjectedYear]
      ,[EstimatedEmp]
      ,[ProjectedEmp]
      ,[PctEstInd]
      ,[PctEstOcc]
      ,[PctProjInd]
      ,[PctProjOcc]
      ,[NumericChange]
      ,[PercentChange]
      ,[GrowthRate]
      ,[GrowthCode]
      ,[Exits]
      ,[AnnualExits]
      ,[Transfers]
      ,[AnnualTransfers]
      ,[Change]
      ,[AnnualChange]
      ,[Openings]
      ,[AnnualOpenings]
      ,[Suppress]
  FROM [dbo].[ProjectionsMatrix] i
  LEFT JOIN GEOGRAPHIES g
  on i.stfips=g.stfips and i.areatype=g.areatype and i.areatypeversion=g.areatypeversion and i.area=g.area
  LEFT JOIN IndDirectories d
  on i.periodyear=d.periodyear and i.periodtype=d.periodtype and i.period=d.period and i.projectedyear=d.projectedyear and i.matrixindcode=d.matrixindcode
  LEFT JOIN OccDirectories o
  on i.periodyear=o.periodyear and i.periodtype=o.periodtype and i.period=o.period and i.projectedyear=o.projectedyear and i.matrixocccode=o.matrixocccode
  --Do not include suppressed values for applications
  where Suppress=0 
  --Only include current Projections in publications
  and i.areatype+i.periodtype+i.periodyear+i.projectedyear in 
		(select areatype+periodtype+max(periodyear)+max(projectedyear) from projectionsmatrix
		group by areatype, periodtype)


--CES
create view a_CES as
SELECT c.[StFips]
      ,c.[AreaType]
      ,c.[AreaTypeVersion]
      ,c.[Area]
	  ,[AreaName]
      ,c.[PeriodYear]
      ,c.[PeriodType]
      ,c.[Period]
	  ,[PeriodDesc]
      ,c.[SeriesCode]
	  ,[SeriesTitle]
      ,[Adjusted]
      ,[Benchmark]
      ,[Prelim]
      ,[EmpCES]
      ,[EmpProductionWorkers]
      ,[EmpFemaleWorkers]
      ,[HoursPerWeek]
      ,[EarningsPerWeek]
      ,[EarningsPerHour]
      ,[HoursAllWorkers]
      ,[EarningsAllWorkers]
      ,[HourlyEarningsAllWorkers]
      ,[SuppRecord]
      ,[SuppHoursEarnings]
      ,[SuppProdWorkers]
      ,[SuppFemaleWorkers]
	  ,[SuppHEAllWrkr]
  FROM [dbo].[CES] c
  LEFT JOIN GEOGRAPHIES g
  on c.stfips=g.stfips and c.areatype=g.areatype and c.areatypeversion=g.areatypeversion and c.area=g.area
  LEFT JOIN Periods p
  on c.periodtype=p.periodtype and c.period=p.period
  LEFT JOIN CESCODES d
  on c.seriescodetype=d.seriescodetype and c.seriescode=d.seriescode
  --Do not include suppressed values for applications
  where SuppRecord=0 and SuppHoursEarnings=0 and SuppProdWorkers=0 and SuppFemaleWorkers=0 and SuppHEAllWrkr=0


--QCEW
create view a_QCEW as 
SELECT i.[StFips]
      ,i.[AreaType]
      ,i.[AreaTypeVersion]
      ,i.[Area]
	  ,[AreaName]
      ,i.[PeriodYear]
      ,i.[PeriodType]
      ,i.[Period]
	  ,[PeriodDesc]
      ,i.[IndCodeType]
      ,i.[IndCode]
	  ,[NAICSTitle]
      ,i.[Ownership]
	  ,OwnerTitle
      ,[Prelim]
      ,[Firms]
      ,[Establishments]
      ,[QuarterAvgEmp]
      ,[Month1Emp]
      ,[Month2Emp]
      ,[Month3Emp]
      ,[TopEmployerAvgEmp]
      ,[TotalWages]
      ,[AvgWeeklyWage]
      ,[TaxableWages]
      ,[UIContributions]
      ,[Suppress]
  FROM [dbo].[Industry] i
  LEFT JOIN GEOGRAPHIES g
  on i.stfips=g.stfips and i.areatype=g.areatype and i.areatypeversion=g.areatypeversion and i.area=g.area
  LEFT JOIN Periods p
  on i.periodtype=p.periodtype and i.period=p.period
  LEFT JOIN NAICSCodes d
  on i.indcodetype=d.naicscodetype and i.indcode=d.naicscode
  LEFT JOIN Ownerships o
  on i.ownership=o.ownership 
  --Do not include suppressed values for applications
  where Suppress=0 



--LAUS
create view a_LAUS as 
SELECT c.[StFips]
      ,c.[AreaType]
      ,c.[AreaTypeVersion]
      ,c.[Area]
	  ,AreaName
      ,[PeriodYear]
      ,c.[PeriodType]
      ,c.[Period]
	  ,[PeriodDesc]
      ,[Adjusted]
      ,[Prelim]
      ,[Benchmark]
      ,[LaborForce]
      ,[Employed]
      ,[Unemployed]
      ,[UnemployedRate]
      ,[CLFPRate]
      ,[EmpPopRatio]
  FROM [dbo].[LaborForce] c
    LEFT JOIN GEOGRAPHIES g
  on c.stfips=g.stfips and c.areatype=g.areatype and c.areatypeversion=g.areatypeversion and c.area=g.area
  LEFT JOIN Periods p
  on c.periodtype=p.periodtype and c.period=p.period


--OEWS
create view a_OEWS as 
SELECT c.[StFips]
      ,c.[AreaType]
      ,c.[AreaTypeVersion]
      ,c.[Area]
	  ,AreaName
      ,[PeriodYear]
      ,c.[PeriodType]
      ,c.[Period]
	  ,PeriodDesc
      ,c.[IndCodeType]
      ,c.[IndCode]
	  ,d.CodeTitle as IndustryTitle
      ,c.[OccCodeType]
      ,c.[OccCode]
	  ,o.CodeTitle as OccupationTitle
      ,c.[WageSource]
	  ,WageSourceDesc
      ,[EmpCount]
      ,c.[RateType]
	  ,RateTypeDesc
      ,[ResponseRate]
      ,[MeanWage]
      ,[EntryWage]
      ,[ExperiencedWage]
      ,[Percentile10Wage]
      ,[Percentile25Wage]
      ,[MedianWage]
      ,[Percentile75Wage]
      ,[Percentile90Wage]
      ,[UserDefinedPct]
      ,[UserDefinedPctWage]
      ,[UserDefinedRangeLoPct]
      ,[UserDefinedRangeHiPct]
      ,[UserDefinedRangeMean]
      ,[WageRelativePctError]
      ,[EmpRelativePctError]
      ,[PanelCode]
      ,[SuppressWage]
      ,[SuppressAll]
      ,[SuppressEmp]
  FROM [dbo].[IOWage] c
    LEFT JOIN GEOGRAPHIES g
  on c.stfips=g.stfips and c.areatype=g.areatype and c.areatypeversion=g.areatypeversion and c.area=g.area
  LEFT JOIN Periods p
  on c.periodtype=p.periodtype and c.period=p.period
  LEFT JOIN IndustryCodes d
  on c.stfips=d.stfips and c.indcodetype=d.codetype and c.indcode=d.code
  LEFT JOIN OccupationCodes o
  on c.stfips=o.stfips and c.occcodetype=o.codetype and c.occcode=o.code
  LEFT JOIN WageSources s
  on s.stfips=c.stfips and s.WageSource=c.WageSource
  LEFT JOIN WageRateTypes r
  on r.RateType=c.RateType
  --Do not include suppressed values for applications
  where [SuppressWage]=0 and [SuppressAll]=0 and [SuppressEmp]=0