I have the following table:
OrderID | OldOrderID | Action | EntryDate | Source 1 | NULL | Insert | 2016-01-12| A 1 | NULL | Remove | 2016-01-13| A 2 | NULL | Insert | 2016-01-12| B 3 | NULL | Insert | 2016-01-12| C 4 | 3 | Insert | 2016-01-13| C 4 | NULL | Remove | 2016-01-14| C
I'm trying to query all rows that dont have the Action Remove. A row can also have a child (a child can also have a child). If a child get the Action Remove it should also remove the parent and so on until it gets to the first parent.
The query I'm currently using fails to remove the parent if a child is removed.
WITH Active AS ( SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY OrderID,Source ORDER BY EntryDate DESC) FROM Orders ) SELECT * FROM Active WHERE [Action] <> 'Remove' AND rn = 1;
One other problem is that OldOrderID can be 0 or equal to OrderID insted of NULL.
Can this problem be solved with SQL only or should I programaticly filter a simple query?
EDIT: As I might have explained it badly some extra information:
Each row can make a child of its self ( New OrderID, OldOrderID = prevoius OrderID). Or make a "clone" of its self with a diffrent Action, but the same OrderID.
A child row will be inserted with a OrderID and an OldOrderID that refrences a OrderID from a prevoius row. If a child is updated to indicate it has been removed it will get a new row with the same OrderID, but the OldOrderID might be NULL or it might still contain a value indicating wich row is its parent.
The Source column idicates where the data came from as this data is aggreggated in a single table from 5 difrent sources. The same OrderID number can exitst on multiple sources, but it dosent mean its the same order. Each row also has a Uniqe ID, but I didnt include it in the table as it didn't seem useful.
The exception are rows that get fake children. They have OldOrderID equal to 0 or is the same as OrderID - in this case its the same as if OldOrderID was equal to NULL.
In the example table I only need the row with the OrderID 2. OrderID 1 gets a "clone" and is removed. OrderID 3 gets a child with OrderID 4. This child then gets removed. So the parent, 3, should also get removed.
I hope this clears some of the questions.