title: SQL Server 2005 T-SQL Problem: Need help in omitting records
body: <p>Good day!</p>
  
  <p>I need help in writing a query.. I have records in a table below.. The condition would be no records should be displayed if the succeeding records' new_state was repeated from the previous records(new_state) and if it is changed in the same date..</p>
  
  <p>here record_id 1 has gone through the ff states: 0->1->2->1->3->4->3 in the same day.. state 1 was changed to state 2 then back to state 1 again (id 2 &amp; 3 would not be displayed).. same with state 3 (id 5 &amp; 6 would not be displayed)..</p>
  
  <pre><code>id | record_id| date_changed | old_state | new_state |
  1  | 1        | 2009-01-01   | 0         | 1         |
  2  | 1        | 2009-01-01   | 1         | 2         | not displayed
  3  | 1        | 2009-01-01   | 2         | 1         | not displayed
  4  | 1        | 2009-01-01   | 1         | 3         |
  5  | 1        | 2009-01-01   | 3         | 4         | not displayed
  6  | 1        | 2009-01-01   | 4         | 3         | not displayed
  </code></pre>
  
  <p>so the result would display only 2 records for record_id=1..</p>
  
  <pre><code>id | record_id| date_changed | old_state | new_state |
  1  | 1        | 2009-01-01   | 0         | 1         |
  4  | 1        | 2009-01-01   | 1         | 3         |
  </code></pre>
  
  <p>Here's the code for table creation and data:</p>
  
  <pre><code>IF OBJECT_ID('TempDB..#table','U') IS NOT NULL
        DROP TABLE #table
  CREATE TABLE #table
  (
  id INT identity primary key,
  record_id INT,
  date_changed DATETIME,
  old_state INT,
  new_state INT
  )
  INSERT INTO #table(record_id,date_changed,old_state,new_state)
      SELECT 1,'2009-01-01',0,1 UNION ALL --displayed
      SELECT 1,'2009-01-01',1,2 UNION ALL --not displayed
      SELECT 1,'2009-01-01',2,1 UNION ALL --not displayed
      SELECT 1,'2009-01-01',1,3 UNION ALL --displayed
      SELECT 1,'2009-01-01',3,4 UNION ALL --not displayed
      SELECT 1,'2009-01-01',4,3           --not displayed
  
  INSERT INTO #table(record_id,date_changed,old_state,new_state)
      SELECT 3,'2009-01-01',0,1 UNION ALL --displayed
      SELECT 3,'2009-01-01',1,2 UNION ALL --not displayed
      SELECT 3,'2009-01-01',2,3 UNION ALL --not displayed
      SELECT 3,'2009-01-01',3,4 UNION ALL --not displayed
      SELECT 3,'2009-01-01',4,1           --not displayed
  
  SELECT * FROM #table
  </code></pre>
  
  <p>I would appreciate any help..</p>
  
  <h2>Thanks</h2>
  
  <p>For clarity regarding record_id=3.. Given this table:</p>
  
  <pre><code>id | record_id| date_changed | old_state | new_state |
  7  | 3        | 2009-01-01   | 0         | 1         |
  8  | 3        | 2009-01-01   | 1         | 2         | not displayed
  9  | 3        | 2009-01-01   | 2         | 3         | not displayed
  10 | 3        | 2009-01-01   | 3         | 4         | not displayed
  11 | 3        | 2009-01-01   | 4         | 1         | not displayed
  </code></pre>
  
  <p>when running the query for record_id=3, the table result will be:</p>
  
  <pre><code>id | record_id| date_changed | old_state | new_state |
  7  | 3        | 2009-01-01   | 0         | 1         |
  </code></pre>
  
  <h2>Thanks!</h2>
  
  <p>UPDATE (12/2/2009):</p>
  
  <p><strong><em>Special scenario</em></strong></p>
  
  <pre><code>id | record_id| date_changed | old_state | new_state |
  1  | 4        | 2009-01-01   | 0         | 1         | displayed
  2  | 4        | 2009-01-01   | 1         | 2         | displayed
  3  | 4        | 2009-01-01   | 2         | 3         | not displayed
  4  | 4        | 2009-01-01   | 3         | 2         | not displayed
  5  | 4        | 2009-01-01   | 2         | 3         | displayed
  6  | 4        | 2009-01-01   | 3         | 4         | not displayed
  7  | 4        | 2009-01-01   | 4         | 3         | not displayed
  </code></pre>
  
  <p>where new_state 3 appears on id 3,5 and 7.. id 3 would not be displayed since it is between id 2 and id 4 which have the same new_state(3).. Then id 5 should be displayed since there is no existing new_state 3 yet..</p>
  
  <p>code snippet:</p>
  
  <pre><code>IF OBJECT_ID('TempDB..#tablex','U') IS NOT NULL
        DROP TABLE #tablex
  
  CREATE TABLE #tablex
  (
  id INT identity primary key,
  record_id INT,
  date_changed DATETIME,
  old_state INT,
  new_state INT
  )
  INSERT INTO #tablex(record_id,date_changed,old_state,new_state)
      SELECT 4,'2009-01-01',0,1 UNION ALL --displayed
      SELECT 4,'2009-01-01',1,2 UNION ALL --displayed
      SELECT 4,'2009-01-01',2,3 UNION ALL --not displayed
      SELECT 4,'2009-01-01',3,2 UNION ALL --not displayed
      SELECT 4,'2009-01-01',2,3 UNION ALL --displayed
      SELECT 4,'2009-01-01',3,4 UNION ALL --not displayed
      SELECT 4,'2009-01-01',4,3           --not displayed
  </code></pre>
  
  <p>I think the sequence in building the result is important..</p>
  
  <p>Thanks!</p>