title: How to union with a distinct column and null rows
body: <p>I am trying to get a list of persons status in SQL Server.</p>
  
  <p>New records are added each day when a person clocks in, so rows do not exist for the current day, however there will be a record of that person clocking in at least one in the table history.</p>
  
  <p><strong>Example Data</strong></p>
  
  <pre><code>| Name | Status | Date                    |   
  |------+--------+-------------------------|  
  | Joe  | In     | 2019-09-03 07:56:00.000 |
  | Jack | In     | 2019-09-03 06:52:00.000 |
  | Joe  | In     | 2019-08-21 07:02:00.000 |
  | Jack | In     | 2019-08-14 06:09:00.000 |
  | Jane | In     | 2019-08-15 07:38:00.000 |
  </code></pre>
  
  <p><strong>Example SQL</strong></p>
  
  <pre><code>select name, status
  from timeclock
  where clockInTime &gt;= dateAdd(day, 0, dateDiff(day, 0, current_timestamp))
  
  union
  
  select name, status
  from timeclock
  where not exists (?)
  </code></pre>
  
  <p>The first select will incorporate Joe and Jack since hey have clocked in in the last 24 hours.</p>
  
  <p><strong>Expected Output</strong></p>
  
  <pre><code>| Name | Status |   
  |------+--------+    
  | Joe  | In     |  
  | Jack | In     |  
  | Jane | Out    |  
  </code></pre>
  
  <p>How do I also select each member only once (distinct), but cast Jane's status as out?</p>