Recursive CTE query looping until conditions are met

Question

I'm trying to write a query where it will recursively seek to find inception or the part number has changed.

There is a single table stktrans that holds the IN and the OUT transactions of any orders.

When something is sent OUT it will have an IN_Nr which can be used to trace back the the IN transaction. The OrderNr links the IN to the OUT

EDIT: Given any IN_Nr I'd like to be able to trace back to the original order it was purchase on. In the table below - TransID 1. But showing the full timeline of the events like the table below.

TransID	Part	IN_Nr	OUT_Nr	OrderNr	Type
8	123-1	232753	232233	888777	OUT
7	123-1	232753	NULL	125707	IN
6	123-1	203944	224789	125707	OUT
5	123-1	203944	NULL	123332	IN
4	123-1	179409	198306	123332	OUT
3	123-1	179409	NULL	111222	IN
2	123-1	176573	171516	111222	OUT
1	123-1	176573	NULL	666000	IN

The patter between the tracing back is as in the picture below:

Currently, I have a very un-dynamic query which can trace back 4 levels from any given IN_Nr:

    DECLARE @IN_Nr INT = 232753
    
    
    ;WITH StkOUT_CTE AS (
            SELECT 
                ST.TransID, 
                ST.Part, 
                ST.IN_Nr, 
                ST.OUT_Nr,
                ST.OrderNr, 
                ST.Type
            FROM 
                StkTrans ST 
            WHERE
                OUT_Nr IS NOT NULL
                AND ST.Type = 'OUT'
    ),
    StkIN_CTE AS (
            SELECT 
                ST.TransID, 
                ST.Part, 
                ST.IN_Nr, 
                ST.OUT_Nr,
                ST.OrderNr, 
                ST.Type
            FROM 
                StkTrans ST 
                INNER JOIN StkOUT_CTE SI ON SI.IN_Nr = ST.IN_Nr
            WHERE
                ST.Type = 'IN'
    )
    SELECT 
           *
      FROM 
      (
              SELECT 
                1 RowOrder, *
              FROM 
              StkOUT_CTE  
              WHERE IN_Nr = @IN_Nr
          UNION
              SELECT 
                2 RowOrder, *
              FROM 
              StkIN_CTE
              WHERE IN_Nr = @IN_Nr
          UNION
            SELECT 3 RowOrder, *
            FROM 
                StkOUT_CTE
            WHERE
                OrderNr = (SELECT OrderNr FROM StkIN_CTE WHERE IN_Nr = @IN_Nr)
          UNION
            SELECT 4 RowOrder, *
            FROM 
                StkIN_CTE
            WHERE
                IN_Nr = (
                    SELECT IN_Nr
                    FROM 
                        StkOUT_CTE
                    WHERE
                        OrderNr = (SELECT OrderNr FROM StkIN_CTE WHERE IN_Nr = @IN_Nr))
        ) ST

To Create a similar setup:

    CREATE TABLE StkTrans (
        TransID INT, 
        Part VARCHAR(25), 
        IN_Nr INT, 
        OUT_Nr INT, 
        OrderNr INT, 
        Type    VARCHAR(3)
    )

    INSERT INTO StkTrans
    (TransID, Part, IN_Nr, OUT_Nr, OrderNr, Type)
    VALUES
    (1, '123-1', 176573, NULL, 666000, 'IN' ),
    (2, '123-1', 176573, 171516, 111222, 'OUT' ),
    (3, '123-1', 179409, NULL, 111222, 'IN' ),
    (4, '123-1', 179409, 198306, 123332, 'OUT' ),
    (5, '123-1', 203944, NULL, 123332, 'IN' ),
    (6, '123-1', 203944, 224789, 125707, 'OUT' ),
    (7, '123-1', 232753, NULL, 125707, 'IN' ),
    (8, '123-1', 232753, 232233, 888777, 'OUT' )

Any guidance that might point me in the right direction on how to use the CTE recursively would be great!

Answer 1

Reading between the lines, perhaps this is what you want?

WITH rCTE AS(
    SELECT *,
           1 AS Iteration
    FROM dbo.StkTrans ST
    WHERE ST.IN_Nr = '232753'
      AND ST.[Type] = 'IN'
    UNION ALL
    SELECT STi.*,
           r.Iteration + 1
    FROM dbo.StkTrans STo
         JOIN dbo.StkTrans STi ON STo.IN_Nr = STi.IN_Nr                     
         JOIN rCTE r ON STo.OrderNr = r.OrderNr
    WHERE STo.[Type] = 'Out'
      AND STi.[Type] = 'In')
SELECT TOP (1) TransID
FROM rCTE
ORDER BY ROW_NUMBER() OVER (ORDER BY Iteration DESC);

db<>fiddle

---

Edit: Perhaps this is what you are therefore after. I now parametrise the first dataset in the rCTE as well, for ease of testing against other values.

DECLARE @IN_Nr int = 232753;

WITH rCTE AS(
    SELECT V.*,
           1 AS Iteration
    FROM dbo.StkTrans STo
         JOIN dbo.StkTrans STi ON STo.IN_Nr = STi.IN_Nr      
         CROSS APPLY (VALUES(STo.TransID,STo.Part,STo.IN_Nr,STo.OUT_Nr,STo.OrderNr,STo.[Type]),
                            (STi.TransID,STi.Part,STi.IN_Nr,STi.OUT_Nr,STi.OrderNr,STi.[Type]))V(TransID,Part,IN_Nr,OUT_Nr,OrderNr,[Type])
    WHERE STo.IN_Nr = @IN_Nr
      AND STo.[Type] = 'OUT'
      AND STi.[Type] = 'In'
    UNION ALL
    SELECT V.*,
           r.Iteration + 1
    FROM dbo.StkTrans STo
         JOIN dbo.StkTrans STi ON STo.IN_Nr = STi.IN_Nr                     
         JOIN rCTE r ON STo.OrderNr = r.OrderNr
                    AND r.[Type] = 'In'
         CROSS APPLY (VALUES(STo.TransID,STo.Part,STo.IN_Nr,STo.OUT_Nr,STo.OrderNr,STo.[Type]),
                            (STi.TransID,STi.Part,STi.IN_Nr,STi.OUT_Nr,STi.OrderNr,STi.[Type]))V(TransID,Part,IN_Nr,OUT_Nr,OrderNr,[Type])
    WHERE STo.[Type] = 'Out'
      AND STi.[Type] = 'In')
SELECT TransID,
       Part,
       IN_Nr,
       OUT_Nr,
       OrderNr,
       [Type]
FROM rCTE
ORDER BY TransID DESC;

db<>fiddle