title: Select row which not followed by specific one
body: <p>I have table with list of candidates and linked tabled with history of candidate statuses:</p>
  
  <pre><code>CandidateId  FirstName  LastName  
  --------------------------------
  1            User       One
  2            User       Two
  </code></pre>
  
  <p>and</p>
  
  <pre><code>CandidateStatusId   CandidateId  Status         Timestamp
  --------------------------------------------------------
  1                   1            Assigned         ...
  2                   1            Interviewed      ...
  3                   1            Offer Accepted   ...
  
  1                   2            Assigned         ...
  2                   2            Interviewed      ...
  3                   2            Offer Accepted   ...
  4                   2            Hired            ...
  5                   2            Bench            ...
  6                   2            Hired            ...
  
  1                   3            Assigned         ...
  2                   3            Interviewed      ...
  3                   3            Offer Accepted   ...
  4                   3            Hired            ...
  5                   3            Bench            ...
  </code></pre>
  
  <p>I want select candidates which has last status is 'Offer Accepted' and never before was 'Hired'. In my example only 1st user should be selected because second already hired and third was hired before (and actually on bench).</p>
  
  <p><strong>UPD:</strong> I prepared SQL statement which should filter users but not sure about its speed, number of users may be quite big:</p>
  
  <pre><code>SELECT * FROM dbo.CandidatePositionStatus
  WHERE CandidateId=34841
  AND 'Hired' NOT IN (SELECT Status FROM dbo.CandidatePositionStatus WHERE CandidateId=34841)
  </code></pre>
  
  <p>But I do not know how to embed it in another select to provide CandidateId</p>
  
  <p><strong>UPD2:</strong> I prepared another query, but it is just checking whether candidate has OA status and hasn't 'HR' status, but speed of query is still opened question.</p>
  
  <pre><code>SELECT DISTINCT CandidateId
  FROM dbo.CandidatePositionStatus
  WHERE
      CandidateId IN (
          SELECT CandidateId FROM dbo.CandidatePositionStatus WHERE PositionStatusForCandidateCode='Offer Accepted' AND FirstWorkingDay IS NOT NULL
      )
      AND CandidateId NOT IN (
          SELECT CandidateId FROM dbo.CandidatePositionStatus WHERE PositionStatusForCandidateCode='Gired'
      )
  </code></pre>