title: TSQL: Count on outer joined tables is producing incorrect results
body: <p>Producing a report on SQL Server 2008. I have joined one table with five others using <code>LEFT OUTER JOIN</code>. When I run a count against those other tables, I get incorrect data. I know WHY, but I'm unsure how to fix it.</p>
  
  <p>The query is tracking admissions candidates for a school. As they move through the process, they are flagged at each major stage of the process. What I need is to count how many passed through a particular stage in a particular period (year and month). Mostly it works. However, if a candidate passed through ANY stage in the period, that candidate gets "counted" in previous stages as well, even if they happened prior to the queried period. An excellent example is AD_35, where there should be one person for a particular academic program, but the output says 2. When I query ONLY the AD_35 table, I get the correct information. So I know the issue is with the outer join, but I am not sure how to overcome this issue (having tried various criteria in the subquery that generates my named outputs). Should be an easy one for someone... Thanks in advance, and the code is below. The :Year and :Month are for user inputs and would be populated with numeric values (e.g. 2015 1)</p>
  
  <p>CW</p>
  
  <pre><code>SELECT DISTINCT 
     ad_candidacy.prog_cde,   
     ad_candidacy.stageyr,   
     ad_candidacy.stagemo,   
     Count (case when (ad_02.stageyr in (:Year, :Year -1, :Year-2) and ad_02.stagemo &lt;= :month) then 1 else null end) as Inquiry,   
     Count (case when (ad_05.stageyr in (:Year, :Year -1, :Year-2) and ad_05.stagemo &lt;= :month) then 1 else null end) as Applied,   
     Count (case when (ad_35.stageyr in (:Year, :Year -1, :Year-2) and ad_35.stagemo &lt;= :month and ad_35.id_num = ad_candidacy.id_num and ad_35.stageyr = ad_candidacy.stageyr and ad_35.stagemo=ad_candidacy.stagemo) then 1 else null end) as Accepted,   
     Count (case when (ad_50.stageyr in (:Year, :Year -1, :Year-2) and ad_50.stagemo &lt;= :month) then 1 else null end) as Matriculated,   
     Count (case when (ad_enroll.stageyr in (:Year, :Year -1, :Year-2) and ad_enroll.stagemo &lt;= :month) then 1 else null end) as Enrolled,   
     ad_candidacy.stagemo_long  
  FROM 
     ad_candidacy 
  LEFT OUTER JOIN 
     ad_02 ON ad_candidacy.id_num = ad_02.id_num 
  LEFT OUTER JOIN 
     ad_05 ON ad_candidacy.id_num = ad_05.id_num 
  LEFT OUTER JOIN 
     ad_35 ON ad_candidacy.id_num = ad_35.id_num 
  LEFT OUTER JOIN 
     ad_enroll ON ad_candidacy.id_num = ad_enroll.id_num 
  LEFT OUTER JOIN 
     ad_50 ON ad_candidacy.id_num = ad_50.id_num  
  WHERE 
     (ad_candidacy.stageyr in (:Year, :Year -1, :Year-2) ) 
     AND ( ad_candidacy.stagemo &lt;= :Month )   
  GROUP BY 
     ad_candidacy.prog_cde,   
     ad_candidacy.stageyr,   
     ad_candidacy.stagemo,   
     ad_candidacy.stagemo_long  
  ORDER BY 
     ad_candidacy.stageyr ASC  
  </code></pre>