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() 
  FROM Orders
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.


This is one way to do it:

IF OBJECT_ID('tempdb..#Orders') IS NOT NULL
    DROP TABLE #Orders;

    OrderID int,
    OldOrderID int,
    Action varchar(10),
    EntryDate date,
    Source char(1)

-- Your sample data, plus a couple more rows 
--to demonstrate the recursive query
    (    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'),
    (    5   ,       4       ,   'Insert'  , '2016-01-15',   'C'),
    (    5   ,     NULL      ,   'Remove'  , '2016-01-16',   'C');

WITH SourceData AS (
    -- Crosstab to get all orders on a single row
    -- The assumption is you can get just two actions (insert and remove)
    SELECT OrderId, 
        ParentOrder = MAX(OldOrderID), 
        InsertDate = MAX(CASE WHEN Action = 'Insert' THEN EntryDate END), 
        RemoveDate = MAX(CASE WHEN Action = 'Remove' THEN EntryDate END), 
        Source = MAX(Source)
    FROM #Orders
    GROUP BY OrderId
OrderHierarchy AS (
    -- Recursive query to get top OrderId
    SELECT *, 
        OrderID AS TopOrderId
    FROM SourceData
    WHERE ParentOrder IS NULL


    SELECT SD.*, OH.TopOrderId
    FROM SourceData AS SD
    INNER JOIN OrderHierarchy AS OH
        ON SD.ParentOrder = OH.OrderID
AllOrders AS (
    -- Get MAX(RemoveData) for the order chain
    SELECT *, MaxRemovedDate = MAX(RemoveDate) OVER (PARTITION BY TopOrderId)
    FROM OrderHierarchy
-- Return just the rows that have no remove date in the chain
FROM AllOrders
WHERE MaxRemovedDate IS NULL;
| improve this answer | |
  • Adding Source to the group by in the SourceData query makes this query run just like I need it to, just a bit slow if to much data is selected, but that's understandable. – Hauba Jan 18 '16 at 11:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.