title: Is there a way to identify a group of rows to use based on the content of those rows by date?
body: <p>I am looking for sets of rows per appointment date that DO NOT have "chart complete" as one of the statuses; how can I go about doing this?</p>
  
  <p>I am attempting to find appointments in our system that have not yet been marked as "chart complete"; this is to identify appointments where notes have not yet been done.</p>
  
  <p>I have tried using NOT EXISTS to weed out the "chart complete" statuses, but no rows return.</p>
  
  <p>My code is as follows (table names changed to hide identity of actual database):</p>
  
  <pre><code>select x.med_rec_nbr as 'MRN', convert(date, y.appt_date) as 'Appt Date', 
  z.txt_appt_time, z.txt_status, z.txt_time
  from table1 x (nolock) join table2 y (nolock) on x.person_id = 
  y.person_id
  join table3 c (nolock) on y.person_id = c.person_id and y.enc_id = 
  c.enc_id
  join table4 z (nolock) on c.person_id = z.person_id and c.enc_id = 
  z.enc_id
  where y.appt_date &gt;= '20161001'
  order by MRN, y.appt_date desc, x.create_timestamp desc
  </code></pre>
  
  <p>Some example rows I am getting are as follows:</p>
  
  <pre><code>MRN             Appt Date   txt_appt_time   txt_status      txt_time
  000000000002    2019-01-21  10:00 AM        CHART COMPLETE  1:23 PM
  000000000002    2019-01-21  10:00 AM        checked out    10:35 AM
  000000000002    2019-01-21  10:00 AM        discharged     10:25 AM
  000000000002    2019-01-21  10:00 AM        with provider  10:18 AM
  000000000002    2019-01-21  10:00 AM        waiting for provider    10:12 AM
  000000000002    2019-01-21  10:00 AM        onboarding     10:05 AM
  000000000002    2019-01-14  1:45 PM         CHART COMPLETE  5:34 PM
  000000000002    2019-01-14  1:45 PM         checked out     2:26 PM
  000000000002    2019-01-14  1:45 PM         discharged      2:24 PM
  000000000002    2019-01-14  1:45 PM         ready for clinical discharge     
  2:15 PM
  000000000002    2019-01-14  1:45 PM         with provider   2:10 PM
  000000000002    2019-01-14  1:45 PM         waiting for provider    1:48 PM
  000000000002    2019-01-14  1:45 PM         onboarding  1:42 PM
  </code></pre>
  
  <p>The code I am attempting to use is:</p>
  
  <pre><code>......
  and NOT EXISTS (select x.med_rec_nbr as 'MRN', convert(date, 
  y.appt_date) as 'Appt Date', z.txt_appt_time, z.txt_status, 
  z.txt_time
  from table1 x (nolock) join table2 y (nolock) on x.person_id = 
  y.person_id
  join table3 c (nolock) on y.person_id = c.person_id and 
  y.enc_id = c.enc_id
  join table4 z (nolock) on c.person_id = z.person_id 
  and c.enc_id = z.enc_id
  where y.appt_date &gt;= '20161001' and z.txt_status = 'chart complete')
  </code></pre>
  
  <p>Expected Results:</p>
  
  <pre><code>0000000000002   2019-01-14     2:00 PM         waiting for provider   1:52 PM
  0000000000002   2019-01-14     2:00 PM         onboarding             1:50 PM
  </code></pre>
  
  <p>and so on.</p>
  
  <p>Actual results:
  none</p>