title: SQL Server 2012 Max Date in Subquery in a joined table
body: <p>I manage a state wide application and use <code>Tableau</code> to create visualizations of data.</p>
  
  <p>I have been tasked with creating a visualization that show how much time is passing between contact entries and today (Case Note Dates). I  know how to isolate the max case note date in the case note table:</p>
  
  <pre><code>Select 
      [Case_Master_ID],
      [Case_Note_Date],
      [Case_Note_Category_Desc],
      [Case_Note_Summary_Narr]
  From 
      buCase_Note 
  Where 
      Case_Note_Date = (Select MAX(Case_Note_Date)
                        From buCase_Note)
  </code></pre>
  
  <p>This query will show me that max case notes in the table from today. The issue is I need to show the max case note for all participants, not just the ones from today. The original query I have been using to view case notes is:</p>
  
  <pre><code>Select
      vc.[_Case Master ID],
      vc.[_Caseload Assignment Current],
      vc.[_Participant Name],
      vc.[Case Status],
      vc.[Reporting Structure Level 4],
      vc.[Reporting Structure Level 5],
      vc.[Application Date],
      vc.[Eligibility Date],
      vc.[Eligibility Determination Extension Date],
      vc.[Eligibility Extended To Date], 
      vc.[Days in Application],
      cn.[Case_Note_Date], 
      cn.[Case_Note_Category_Desc],
      cn.[Case_Note_Summary_Narr]
  From  
      biVR_Cases vc
  Left outer Join 
      buCase_Note cn ON cn.Case_Master_ID = vc.[_Case Master ID]
  </code></pre>
  
  <p>I need to keep <code>biVR_Cases</code> on the left to show all the open clients. Then I need to join in the case note table and for every participant, I want to show their max case note date. When I add this to the end of the above query:</p>
  
  <pre><code>Where cn.[Case_Note_Date] = (
      Select
          MAX(cn.Case_Note_Date)
      From buCase_Note)
  </code></pre>
  
  <p>I get the following error is SSMS 2012:</p>
  
  <blockquote>
    <p>An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.</p>
  </blockquote>
  
  <p>I am looking to retain the <code>bi</code> table on the left while successfully joining in the case note table and bringing in only the most recent case note per participant.</p>
  
  <p>Adding details:
  Of course,
  Here is a sample of data I get when running the following query:</p>
  
  <pre><code>Select
   vc.[_Case Master ID],
   vc.[_Caseload Assignment Current],
   vc.[_Participant Name],
  cn.[Case_Note_Date],
  From  biVR_Cases vc
  LEFT outer JOIN buCase_Note cn ON vc.[_Case Master ID] = cn.Case_Master_ID
  </code></pre>
  
  <p>_Caseload Assignment Current  Test Participant Name Casenote Date
  Test Counselor                Participant A
                                                       September 29, 2010
                                                   September 23, 2010
                                                   August 30, 2010
                                                   June 30, 2010
                                                   June 1, 2010</p>
  
  <p>The bi table contains participant information like name, application, case master ID, etc. The casenote table contains the case master ID as well hence the join. It also contains the dates each entry is created. So for the dataset above, I am trying to only bring in the most recent casenote for each participant. I only included 1 in the sample above, but we have over 15,000. Each participant will have many case notes. I am trying to grab the top casenote so I can calculate the date difference between the most recent case note and today for each participant. When I add  :</p>
  
  <pre><code>Where cn.[Case_Note_Date] = (Select
  top 1 [Case_Note_Date]
  From buCase_Note
  Order by 1 DESC))
   OR 
  Where Case_Note_Date=(
  Select
  MAX(Case_Note_Date)
  From buCase_Note)
  </code></pre>
  
  <p>It is only showing the top or max casenote for participants that had a casenote created today. Instead of showing the max casenote in the casenote table, I need the max casenote per participant. I hope that makes more sense.</p>