title: How do I filter a history table for items that had a specific status but exclude that item if it also had another specific status
body: <p>In our SQL Server 2008 database, we have a one-to-many relationship between the "Jobs" table and the "History" table. Each time the status of the job is changed, we log the Status, date-time and Job ID into the History table.</p>
  
  <p>I need to get all the Job ID’s where the  history  has a status = “Issued” <strong>unless</strong> the job history also has a status = “Revoked”.</p>
  
  <p>If the history for a Job has a status = “Revoked”, but then at a <strong>later date</strong> has a status = “Issued” , it must be included in the results.</p>
  
  <p>So based on the following mock up table and the rules above, the results that should be returned are FK_JobID’s 2 and 3. </p>
  
  <pre><code>DECLARE @History TABLE 
  ( 
      HistoryID   INT IDENTITY(1,1), 
      MaterStatus VARCHAR(300), 
      Updated     DATETIME, 
      FK_JobID    INT
  )
  
  INSERT INTO @History 
  VALUES ('Issued','2015-09-09',1), 
         ('Revoked','2015-09-09',1),
         ('Issued','2015-09-09',2),
         ('Archived','2015-09-09',2),
         ('Issued','2015-09-09',3),
         ('Revoked','2015-09-09',3),
         ('Issued','2015-09-10',3),
         ('other','2015-09-09',4);
  </code></pre>
  
  <p>How do I write this query to do that?</p>