title: Count maximum rows with FK in any 8-week period
body: <p>A <a href="https://stackoverflow.com/questions/28918461/efficiently-identify-all-fk-items-with-n3-dates-within-any-8-week-period-from-a">similar post</a> does not quite meet my needs.  I'm using SSMS (SQL server).  I want to add a column for the count of appointments by patient within an 8-week period (ApptDate to ApptDate + 56).  What's the best way to go about this?  (Any example code would be appreciated.)  The data table looks a bit like this:</p>
  <pre><code>ApptID      PatientID    ApptDate 
  ----------------------------------
  Appt1       Pt1          Datetime1
  Appt2       Pt1          Datetime2
  Appt3       Pt2          Datetime3
  ...         ...          ...
  </code></pre>
  <p>I'd like the output to look like this:</p>
  <pre><code>ApptID     PatientID     ApptDate    CountOfVisitInNext8Wks (i.e. Datetime to Datetime+56)
  -----------------------------------------------------------
  Appt1      Pt1           Datetime1   2
  Appt2      Pt1           Datetime2   1
  Appt3      Pt2           Datetime3   7
  ...        ...           ...         ...
  </code></pre>
  <p>I plan to use the output table above to create another table showing the max count by patient:</p>
  <pre><code>PatientID   MaxCountOfVisitInNext8Wks
  -------------------------------------
  Pt1         2
  Pt2         7
  </code></pre>
  <p>My data set is small, so efficiency is not a concern.  I don't know how best to approach this (e.g. subqueries, inner join as in post linked above, while loop with a date parameter).  I'm quite the beginner with SQL.  Thanks in advance.</p>