title: How to handle NULL in a SQL subquery?
body: <p>I'm pulling my hair out over such a simple thing...</p>
  
  <p>I'm recording the number of days a member attends a gym club. By default, I assume the member attends every day. When they are sick, I record the dates and total number of days absent in a table (ie <code>DateFrom</code>, <code>DateEnd</code>, <code>TotalDays</code>).  The total days absent is the difference between <code>DateFrom</code> and <code>DateEnd</code>.</p>
  
  <p>Now sometimes I don't know when the member is coming back to gym. Just that they have stopped attending on a certain day. Hence the <code>DateEnd</code> and <code>TotalDays</code> are unknown. So the total number days are calculated by taking the difference between <code>DateFrom</code> and today's date.</p>
  
  <pre><code>Table: InactiveOnProgram
  Columns: PersonId, DateFrom, DateEnd, TotalDays
  </code></pre>
  
  <p><strong>Data:</strong></p>
  
  <pre><code>1,01/01/2012,05/01/2012,5 
  1,05/01/2012,08/01/2012,3
  2,01/02/2012,05/02/2012,5 
  2,05/02/2012,08/02/2012,3
  2,20/02/2012,null,null
  </code></pre>
  
  <p>My below query works fine for personId=2. The total days absent is 8+2=10 days (2 days being 20/02/2012 till 22/02/2012 = today ). But for personId=1, it returns null , instead of 8 days!</p>
  
  <p>sql:</p>
  
  <pre><code>(SELECT   
      case (  isnull(sum(TotalDays), 0) ) 
          when 0 then 0
          else CAST(SUM(TotalDays)  as DECIMAL(20,2))
      end 
  FROM InactiveOnProgram  
  )    
  +   
  (SELECT 
      case (  isnull( DateFrom, 0) ) 
          when null then 0
          when 0 then 0
          else CAST(datediff(day,DateFrom, getdate()) as DECIMAL(20,2))
      end  
  FROM  InactiveOnProgram   
  WHERE (TotalDays is null or TotalDays =0) 
  AND  DateTo is null 
  )
  </code></pre>
  
  <p>Any idea what I'm missing here?! As far as I can guess the second part of sql returns null and because of this it ignores the first part!</p>
  
  <p>Any help is much appreciated.</p>
  
  <p>Thanks</p>