title: Self join not returning rows when only single row
body: <p>Shown below is a snippet of a resident admit-discharge status table. 
   I am trying to filter out residents whose date (admit/readmit date) 
   is at least 30 days after their last discharge.</p>
  
  <p>The code below seems to be working fine except when there is only one row for the resident.
    Resident 200 (see table) has only one entry and hence not returned by query.</p>
  
  <p>How do I get the query to return this row ??
  Left and Right joins don't give the right results.</p>
  
  <pre><code> ;with sorted as 
    (
       select ROW_NUMBER() over(partition by rsa.cono,rsa.resno order by rsa.sort desc) rn,rsa.*
    from ri_status_admit rsa 
   )
  
  select   s1.cono,s1.resno,s1.rn,s1.date,s1.admit_disch,s2.date,s2.rn,
                  s2.admit_disch,datediff(dd,s2.date, s1.date)
           from sorted s1 
               join sorted s2
           on 
                s1.cono = s2.cono
                and s1.resno = s2.resno 
                and s1.admit_disch in ('A','R')
                and s2.rn = s1.rn + 1
                and datediff(dd,s2.date, s1.date) &gt;= 30 
  
  
   rn cono    resno   date            sort    admit_disch
   1  01     145     7/5/2009     73047           D
   2  01     145     11/23/2007    4872           R
   3  01     145     11/19/2007    54797          D
   4  01     145     4/21/2007    48403           R
   5  01     145     4/20/2007    48400           D
   1  01     152     1/4/2010     77674           D
   2  01     152     6/1/2001        97           A
   1  01     170     9/15/2009    74881           D
   2  01     170     5/15/2004    18733           R
   3  01     170     5/12/2004    18666           D
   4  01     170     3/1/2004     17014           R
   5  01     170     2/17/2004    16686           D
   6  01     170     1/2/2001       104           A
   1  01     200     1/4/2017       100           A
  </code></pre>