CREATE VIEW vwSurveyDimAge AS  
SELECT aID AS ageID,  
  aValue AS Age  
FROM tblSurveyAnswers  
WHERE qID=6
GO

CREATE VIEW vwSurveyDimGender AS  
SELECT aID AS genderID,  
  aValue AS Gender  
FROM tblSurveyAnswers  
WHERE qID=7
GO

CREATE VIEW vwSurveyDimJobStatus AS    
SELECT aID AS jobStatusID,    
  aValue AS jobStatus    
FROM tblSurveyAnswers       
WHERE qID=9
GO

CREATE VIEW vwSurveyDimOtherPlans AS    
SELECT aID otherPlanID,    
  aValue AS otherPlan    
FROM tblSurveyAnswers    
WHERE qID=11
GO

CREATE view vwSurveyDimSalary AS    
SELECT 0 AS salaryID, 'n/a' AS Salary    
UNION     
SELECT 1 AS salaryID, '20,000-34,999' AS Salary    
UNION    
SELECT 2 AS salaryID, '35,000-49,999' AS Salary    
UNION    
SELECT 3 AS salaryID, '50,000-64,999' AS Salary    
UNION    
SELECT 4 AS salaryID, '65,000-79,999' AS Salary    
UNION    
SELECT 5 AS salaryID, '80,000 and Over' AS Salary    
GO

CREATE VIEW vwSurveyDimService AS  
SELECT aID AS serviceID,  
  aValue AS yrsService  
FROM tblSurveyAnswers  
WHERE qID=8
GO

CREATE VIEW vwSurveyFact AS          
SELECT DISTINCT           
  sid,          
  r.r06 AS ageID,          
  r.r07 AS genderID,          
  r.r08 AS serviceID,          
  r.r09 AS jobStatusID,          
  CASE           
   WHEN (p.rate * 2080) < 20000 THEN 0          
   WHEN (p.rate * 2080) BETWEEN 20000 AND 34999 THEN 1          
   WHEN (p.rate * 2080) BETWEEN 35000 AND 49999 THEN 2          
   WHEN (p.rate * 2080) BETWEEN 50000 AND 64999 THEN 3          
   WHEN (p.rate * 2080) BETWEEN 65000 AND 79999 THEN 4          
   WHEN (p.rate * 2080) >= 80000 THEN 5          
   ELSE 0          
  END AS salaryID,          
  CASE r.r12           
   WHEN 'n/a' THEN 0          
   WHEN 'IRA' THEN 1          
   WHEN 'Deferred Compensation' THEN 2    
   WHEN 'Other' THEN 3          
   ELSE 0          
  END AS otherPlanID,          
  CASE r.r01          
   WHEN 1 THEN 1      
   ELSE 0          
  END AS q1y,          
  CASE r.r01          
   WHEN 2 THEN 1          
   ELSE 0          
  END AS q1n,          
  CASE r.r01          
   WHEN 0 THEN 1          
   ELSE 0          
  END AS q1na,          
  CASE r.r02          
   WHEN 1 THEN 1          
   ELSE 0          
  END AS q2y,          
  CASE r.r02          
   WHEN 2 THEN 1          
   ELSE 0          
  END AS q2n,          
  CASE r.r02          
   WHEN 0 THEN 1          
   ELSE 0          
  END AS q2na,          
  CASE r.r03          
   WHEN 1 THEN 1          
   ELSE 0          
  END AS q3y,          
  CASE r.r03          
   WHEN 2 THEN 1          
   ELSE 0          
  END AS q3n,          
  CASE r.r03          
   WHEN 0 THEN 1          
   ELSE 0          
  END AS q3na,          
  CASE r.r04          
   WHEN 1 THEN 1          
   ELSE 0          
  END AS q4y,          
  CASE r.r04          
   WHEN 2 THEN 1          
   ELSE 0          
  END AS q4n,          
  CASE r.r04          
   WHEN 0 THEN 1          
   ELSE 0          
  END AS q4na,          
  CASE r.r05          
   WHEN 1 THEN 1          
   ELSE 0          
  END AS q5y,          
  CASE r.r05          
   WHEN 2 THEN 1          
   ELSE 0          
  END AS q5n,          
  CASE r.r05          
   WHEN 0 THEN 1          
   ELSE 0          
  END AS q5na    
FROM tblSurveyResults r,          
  tblPaychart p          
WHERE p.grade = r.r10          
AND  p.step = r.r11
GO