I need to use a case statement to score some answers from this survey for instance if the answer to the question is Yes then return the value of 1 No 0, On the on question it has highest levele of Education if they answer Elementary = 1 Middle School = 1 Etc you see my attempt in the code below. Any ideas on this? Anyone??
SELECT
HRA_DATA_ALL_03092012.[CREATEDATE],
HRA_DATA_ALL_03092012.[DOB] ,
HRA_DATA_ALL_03092012.[MemberID] ,
HRA_DATA_ALL_03092012.[FirstName],
HRA_DATA_ALL_03092012.[MI] ,
HRA_DATA_ALL_03092012.[LastName] ,
HRA_DATA_ALL_03092012.[HomePhone],
HRA_DATA_ALL_03092012.[CellPhone],
HRA_DATA_ALL_03092012.[EmContactFirstName],
HRA_DATA_ALL_03092012.[EmContactLastName] ,
HRA_DATA_ALL_03092012.[EmContactPhone] ,
HRA_DATA_ALL_03092012.[EmContactCellPhone],
HRA_DATA_ALL_03092012.[POAFirstName],
HRA_DATA_ALL_03092012.[POALastName],
HRA_DATA_ALL_03092012.[POAPhone] ,
HRA_DATA_ALL_03092012.[InterpreterFlag] ,
HRA_DATA_ALL_03092012.[Race] ,
CASE HRA_DATA_ALL_03092012.[HighestEdLevel]
WHEN ‘ELEMENTARY’ THEN ‘ELEMENTARY - 1’
WHEN ‘Middle School’ THEN ‘Middle School - 1’
END
HRA_DATA_ALL_03092012.[SelfCompareHealthOthers] ,
HRA_DATA_ALL_03092012.[SelfCompareHealthYearAgo] ,
HRA_DATA_ALL_03092012.[SelfCompareEmotionalOthers] ,
HRA_DATA_ALL_03092012.[SelfCompareEmotionalYearAgo],
HRA_DATA_ALL_03092012.[HavePCPFlag],
HRA_DATA_ALL_03092012.[HospAdmitPastSixFlag] ,
HRA_DATA_ALL_03092012.[ERPastSixFlag] ,
HRA_DATA_ALL_03092012.[OxygenFlag] ,
HRA_DATA_ALL_03092012.[TrachFlag] ,
HRA_DATA_ALL_03092012.[TubeFeedFlag] ,
HRA_DATA_ALL_03092012.[WheelchairFlag] ,
HRA_DATA_ALL_03092012.[HomeSpeechFlag] ,
HRA_DATA_ALL_03092012.[HomeNurseFlag] ,
HRA_DATA_ALL_03092012.[NebulizerFlag] ,
HRA_DATA_ALL_03092012.[CatheterFlag] ,
HRA_DATA_ALL_03092012.[InsulinPumpFlag],
HRA_DATA_ALL_03092012.[WalkerFlag] ,
HRA_DATA_ALL_03092012.[HomeOccFlag],
HRA_DATA_ALL_03092012.[CPAPFlag] ,
HRA_DATA_ALL_03092012.[OstomyFlag] ,
HRA_DATA_ALL_03092012.[HomeInfusionFlag]
HRA_DATA_ALL_03092012.[HospBedFlag] ,
HRA_DATA_ALL_03092012.[HomePhysFlag],
HRA_DATA_ALL_03092012.[HHDMEOtherFlag] ,
HRA_DATA_ALL_03092012.[GroomingHelp] ,
HRA_DATA_ALL_03092012.[DressingHelp] ,
HRA_DATA_ALL_03092012.[BathingHelp] ,
HRA_DATA_ALL_03092012.[EatingHelp],
HRA_DATA_ALL_03092012.[WalkingHelp] ,
HRA_DATA_ALL_03092012.[TransferHelp] ,
HRA_DATA_ALL_03092012.[SpecialDietFlag],
HRA_DATA_ALL_03092012.[NumDailyMeds] ,
HRA_DATA_ALL_03092012.[OftenSadFlag],
HRA_DATA_ALL_03092012.[AlcoholDrugAbuseHistoryFlag],
HRA_DATA_ALL_03092012.[TobaccoUseFlag] ,
HRA_DATA_ALL_03092012.[CondCHFFlag],
HRA_DATA_ALL_03092012.[CondDiabetesFlag] ,
HRA_DATA_ALL_03092012.[CondCOPDFlag] ,
HRA_DATA_ALL_03092012.[CondHBPFlag] ,
HRA_DATA_ALL_03092012.[CondCancerFlag] ,
HRA_DATA_ALL_03092012.[CondTransplantFlag],
HRA_DATA_ALL_03092012.[CondHIVFlag] ,
HRA_DATA_ALL_03092012.[CondDepressionFlag] ,
HRA_DATA_ALL_03092012.[CondPainFlag] ,
HRA_DATA_ALL_03092012.[CondArthritisFlag] ,
HRA_DATA_ALL_03092012.[CondParkinsonsFlag] ,
HRA_DATA_ALL_03092012.[CondHearingFlag] ,
HRA_DATA_ALL_03092012.[CondVisionFlag] ,
HRA_DATA_ALL_03092012.[CondBreathingFlag],
HRA_DATA_ALL_03092012.[CondCholesterolFlag] ,
HRA_DATA_ALL_03092012.[CondHeartAttackFlag] ,
HRA_DATA_ALL_03092012.[CondStrokeFlag] ,
HRA_DATA_ALL_03092012.[CondLiverFlag] ,
HRA_DATA_ALL_03092012.[CondKidneyFlag],
HRA_DATA_ALL_03092012.[CondUrinaryFlag] ,
HRA_DATA_ALL_03092012.[CondAlzheimersFlag]
FROM HRA_DATA_ALL_03092012;