SQL Server 2005 T-SQL Problem: Need help in omitting records

Question

Good day!

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..

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 & 3 would not be displayed).. same with state 3 (id 5 & 6 would not be displayed)..

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

so the result would display only 2 records for record_id=1..

id | record_id| date_changed | old_state | new_state |
1  | 1        | 2009-01-01   | 0         | 1         |
4  | 1        | 2009-01-01   | 1         | 3         |

Here's the code for table creation and data:

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

I would appreciate any help..

Thanks

For clarity regarding record_id=3.. Given this table:

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

when running the query for record_id=3, the table result will be:

id | record_id| date_changed | old_state | new_state |
7  | 3        | 2009-01-01   | 0         | 1         |

Thanks!

UPDATE (12/2/2009):

Special scenario

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

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..

code snippet:

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

I think the sequence in building the result is important..

Thanks!

Answer 1

Following steps to get to the result

• select all items that should not appear in the result.
• left join these with the original table and select only those records that don't match a should not appear record.

.

;WITH cte_table (master_id, master_state, id, record_id, old_state, new_state, level) AS
(
  SELECT  id, old_state, id, record_id, old_state, new_state, 1
  FROM    #table
  UNION ALL
  SELECT  master_id, master_state, #table.id, #table.record_id, #table.old_state, #table.new_state, level + 1
  FROM    cte_table
          INNER JOIN #table ON cte_table.new_state = #table.old_state
                               AND cte_table.record_id = #table.record_id
                               AND cte_table.id < #table.id                               
                               AND cte_table.master_state < #table.old_state
)
SELECT  master_id, t1.*, level
INTO    #result
FROM    #table t1
        INNER JOIN (
            SELECT    master_id, min_child_id = MIN(id), level
            FROM      cte_table
            GROUP BY  master_id, level
        ) t2 ON t2.min_child_id = t1.id

SELECT  t1.*
FROM    #table t1
        LEFT OUTER JOIN (
            SELECT  r1.id
            FROM    #result r1
                    INNER JOIN (
                        SELECT  r1.master_id
                        FROM    #result r1
                                INNER JOIN #result r2 ON r2.new_state = r1.old_state
                                                         AND r2.master_id = r1.master_id
                        WHERE   r1.level = 1
                    ) r2 ON r2.master_id = r1.master_id
        ) r1 ON r1.id = t1.id
WHERE   r1.id IS NULL
        AND t1.old_state < t1.new_state
ORDER BY 1, 2, 3

Answer 2

SELECT A.*
/*
A.ID, A.old_state, a.new_state, 
B.ID as [Next], b.old_state, b.new_state,
C.ID as [Prev],  c.old_state, c.new_state
*/
FROM #table A LEFT JOIN 
#table B ON A.ID = (B.ID - 1)
LEFT JOIN #table C ON (A.ID - 1) = C.ID
-- WHERE A.old_State <> B.new_State AND A.new_State <> C.old_State
WHERE A.record_id = 1
AND A.old_State <> COALESCE(B.new_State, -1) 
AND A.new_State <> COALESCE(C.old_State, -1)

EDIT: I guess, what OP needs is that the remaining record should be selected except those where current record's old state is not the same as next record's new state (kind of an undo operation in records) and current record's new state should not be same as previous record's old state.