title: Select inside CASE THEN
body: <p>I need to select the project rate or shift rate that has the effective date less than today.</p>
  
  <pre><code> SELECT 
   CASE 
      WHEN ISNULL(s.rate,0) = 0 
      THEN SELECT TOP 1 pr.rate FROM ProjectRates pr WHERE (pr.projectID = p.ID) AND (pr.effectiveDate &lt; GETDATE()) ORDER BY pr.effectiveDate DESC
      --p.rate 
      ELSE SELECT TOP 1 sr.rate FROM ShiftRates sr WHERE (sr.shiftID = s.ID) AND (sr.effectiveDate &lt; GETDATE()) ORDER BY pr.effectiveDate DESC                
      --s.rate 
  END AS rate
  FROM Projects p
  INNER JOIN Shifts s ON (p.ID = s.projectID)
  WHERE (p.ID = @projectID)
  </code></pre>
  
  <p>Please note that this code snippet is part of a larger stored proc and thus it must be within a CASE statement.</p>