title: SQL where clause with nested case statements optimisation
body: <p>Below is my WHERE clause and there is repetition in it (particularly with the two @subgroup variables), so I was wondering if there is anything I can do to optimise the code. My code works fine as is, but if I can get it prettier that would be great. Firstly a quick explanation.</p>
  
  <p>I have five parameters that populate from asp dropdowns. For the purposes of this question I've populated them with data:</p>
  
  <pre><code>DECLARE
  @DataCollection VARCHAR(50) = '2013/14 - Autumn 1 - Targets',
  @StuYear VARCHAR(2) = '11',
  @SubjectName VARCHAR(100) ='English',
  @TeachingGroup VARCHAR(25) = 'Select All',
  @SubGroup VARCHAR(15) = 'FSMYes',
  @SubGroup2 VARCHAR(15) = 'SENS'
  </code></pre>
  
  <p>The where statement enables the result set to be filtered based on the values in the dropdowns.</p>
  
  <p>@DataCollection, @StuYear and @SubjectName are all mandatory and have default values in the dropdown. The rest are optional.</p>
  
  <p>The variables for @subgroup are populated based on hardcoded values in the dropdown, so a parameter is passed and if that parameter value matches the condition in the WHERE clause then a particular db value is filtered to in a particular column of the table.</p>
  
  <p>Here is my SQL:</p>
  
  <pre><code>WHERE
  [StuYear] = @StuYear AND
  [DataCollection] = @DataCollection AND
  [Name] = @SubjectName AND (
      @TeachingGroup = 'Select All' OR 
      [TeachingGroup] = @TeachingGroup
  ) AND (
     @SubGroup = 'Select All' OR
     Gender = CASE --Gender
             WHEN @SubGroup = 'GenF' THEN 'F'
             WHEN @SubGroup = 'GenM' THEN 'M'
         END 
     OR
     LEFT(ks2av,1) = CASE --Attainer
             WHEN @SubGroup = 'High' THEN '5'
             WHEN @SubGroup = 'High' THEN '5'
             WHEN @SubGroup = 'Middle' THEN '4'
             WHEN @SubGroup = 'Low' THEN '3'
             WHEN @SubGroup = 'Low' THEN '2'
             WHEN @SubGroup = 'Low' THEN '1'
         END
     OR
     PupilPremium = CASE --Pupil Premium
             WHEN @Subgroup = 'PPYes' THEN 'Yes'
         END 
     OR
     FSM = CASE --Free School Meals
             WHEN @Subgroup = 'FSMYes' THEN 'Yes'
         END
     OR
     SEN = CASE --SEN
             WHEN @Subgroup = 'SENA' THEN 'A'
             WHEN @Subgroup = 'SENP' THEN 'P'
             WHEN @Subgroup = 'SENS' THEN 'S'
             WHEN @Subgroup = 'SENNo' THEN 'N'
         END
     OR
     (@SubGroup='SENYes' AND SEN IN ('A','P','S')) --SEN Yes
     OR
     EAL = CASE --English as an Additional Language
             WHEN @Subgroup = 'EALYes' THEN 'Yes'
         END
     OR
     LAC = CASE --Looked After Children
             WHEN @Subgroup = 'LACYes' THEN 'Yes'
         END
     OR --Gifted &amp; Talented       
     GandT = CASE 
             WHEN @Subgroup = 'GandTYes' THEN 'Yes'
         END
     OR --Gifted Only       
     Gifted = CASE 
             WHEN @Subgroup = 'GiftedYes' THEN 'Yes'
         END
     OR --Talented Only       
     Talented = CASE 
             WHEN @Subgroup = 'TalentedYes' THEN 'Yes'
         END
  )AND (
     @subgroup2 = 'Select All' OR
     Gender = CASE --Gender
             WHEN @subgroup2 = 'GenF' THEN 'F'
             WHEN @subgroup2 = 'GenM' THEN 'M'
         END 
     OR
     LEFT(ks2av,1) = CASE --Attainer
             WHEN @subgroup2 = 'High' THEN '5'
             WHEN @subgroup2 = 'High' THEN '5'
             WHEN @subgroup2 = 'Middle' THEN '4'
             WHEN @subgroup2 = 'Low' THEN '3'
             WHEN @subgroup2 = 'Low' THEN '2'
             WHEN @subgroup2 = 'Low' THEN '1'
         END
     OR
     PupilPremium = CASE --Pupil Premium
             WHEN @subgroup2 = 'PPYes' THEN 'Yes'
         END 
     OR
     FSM = CASE --Free School Meals
             WHEN @subgroup2 = 'FSMYes' THEN 'Yes'
         END
     OR
     SEN = CASE --SEN
             WHEN @subgroup2 = 'SENA' THEN 'A'
             WHEN @subgroup2 = 'SENP' THEN 'P'
             WHEN @subgroup2 = 'SENS' THEN 'S'
             WHEN @subgroup2 = 'SENNo' THEN 'N'
         END
     OR
     (@subgroup2='SENYes' AND SEN IN ('A','P','S')) --SEN Yes
     OR
     EAL = CASE --English as an Additional Language
             WHEN @subgroup2 = 'EALYes' THEN 'Yes'
         END
     OR
     LAC = CASE --Looked After Children
             WHEN @subgroup2 = 'LACYes' THEN 'Yes'
         END
     OR --Gifted &amp; Talented       
     GandT = CASE 
             WHEN @subgroup2 = 'GandTYes' THEN 'Yes'
         END
     OR --Gifted Only       
     Gifted = CASE 
             WHEN @subgroup2 = 'GiftedYes' THEN 'Yes'
         END
     OR --Talented Only       
     Talented = CASE 
             WHEN @subgroup2 = 'TalentedYes' THEN 'Yes'
         END
  )
  </code></pre>