Databases
StackOverflow2013
Postsdbo
POSelect row which not followed by specific one
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect row which not followed by specific one
    text
    copied!<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>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload