title: Distinct counts based on dates in SQL Server
body: <p>I'm attempting to create a new table based on the following table:</p>
  
  <pre><code>SubjectNumber    TestDates    
  001              11/12/12
  001              01/10/15
  001              04/03/13
  002              05/21/14
  003              08/06/15
  002              09/12/18
  002              03/30/12
  003              09/07/18
  004              10/14/11
  005              02/05/14
  005              02/06/14
  </code></pre>
  
  <p>I need a new table that will include the following: </p>
  
  <p>1) Subject number</p>
  
  <p>2) Their first test date</p>
  
  <p>3) Their last test date</p>
  
  <p>4) A count of the total number of tests</p>
  
  <p>5) A column with 0's and 1's indicating whether or not the subject had any two test dates that were at least 30 days apart</p>
  
  <p>The new table should look like the following:</p>
  
  <pre><code>SubjectNumber    FirstTestDate     LastTestDate     TestCount    ThirtyDaysApart
  001              11/12/12          01/10/15         3            1
  002              03/30/12          09/12/18         3            1
  003              08/06/15          09/07/18         2            1
  004              10/14/11                           1            0
  005              02/05/14          02/06/14         2            0
  </code></pre>
  
  <p>I'm using SQL Server 2017.</p>
  
  <p>I have a temporary table called #Temp1 that I'd like to store the data in. This table is called #Temp. </p>
  
  <pre><code>Insert into #Temp1
  SELECT SubjectNumber, WHERE 
  CASE MIN(TestDates) then FirstTestDate = TestDates
  END
  CASE MAX(TestDates) then LastTestDate = TestDates
  END
  FROM #Temp;
  </code></pre>