title: Using SQL Server 2008, I have a hard coded two tiered case statement that I want to put into a reference table
body: <p>Querying appointment booking data using SQL Server 2008.
  Appointment durations are largely hard-coded depending on the Clinic and appointment type as per this sample:</p>
  
  <pre><code>case when cl.ClinicCode like 'FPLAS%' then case when apt.AppointmentTypeName in ('New','Time Critical','Emergency') then 20 else 10 end
      when cl.ClinicCode like 'SPLAS%' then case when apt.AppointmentTypeName in ('New','Time Critical','Emergency') then 20 else 10 end
  else 30 end as duration 
  </code></pre>
  
  <p>I want to remove the coding and create a table to store this information.  The problem I have is that there are about 50 potential values for apt.AppointmentTypeName which I do not want to repeat for each of the 400 different ClinicCodes.  How do I best deal with the "else" part of the embedded AppointmentTypeName case statement? </p>