title: Problems finding out Active users due to Hire/Resign Date
body: <p>In my <code>Employee</code> table in SQL Server I have two datetime columns: <code>HiringDate</code> and <code>ResignDate</code>. I want to create a new column <code>Status</code> (Active, Inactive) in a view. </p>
  
  <ul>
  <li>If HiringDate is NULL or greater than today = Inactive </li>
  <li>If HiringDate is Active but ResignDate is earlier than today then Status also have to be Inactive. </li>
  </ul>
  
  <p>Do I have to make some kind of nested case to make this work and I'm also wondering do I have to convert it to date format so the time portion are not included. </p>
  
  <p>Would be very thankful for help. </p>
  
  <p>Here is what ive tried so far but it doesnt work properly...</p>
  
  <pre><code>CASE 
              WHEN CONVERT(DATE,HiringDate) IS NOT NULL 
              OR CONVERT(DATE,HiringDate) &lt;= CONVERT(DATE,GETDATE()) 
              THEN 
                  CASE 
                      WHEN CONVERT(DATE,ISNULL(ResignDate, CONVERT(DATE,'2099-12-30'))) &lt;= CONVERT(DATE,GETDATE()) 
                      THEN 'Active'
                      ELSE 'Inactive'
                  END 
              ELSE 'Inactive' 
          END as Status
  </code></pre>