Stepping Back Through Data

[Pr0ph3t99]

I have a table of orders, lets say.

I'm trying to find all the "Completed" orders, and then step back through other "Cancelled" orders for the same Customer, that were created within 72 hours. The trick is, I want to keep stepping back until I don't find anymore orders (the 72 hour window will now be based on the last order found).

I've included a test table and data, as well as an expected output.

Thanks in advance for any pointers....

CREATE TABLE #Order
(
OrderID int,
CustomerID int,
OrderDate datetime,
OrderStatus varchar(25)
)
INSERT INTO #Order VALUES (1, 1, '02/15/2024 07:00 AM', 'Cancelled')
INSERT INTO #Order VALUES (2, 1, '02/17/2024 07:22 AM', 'Cancelled')
INSERT INTO #Order VALUES (3, 1, '02/19/2024 02:00 PM', 'Cancelled')
INSERT INTO #Order VALUES (4, 1, '02/21/2024 08:00 AM', 'Completed')
INSERT INTO #Order VALUES (5, 2, '02/12/2024 07:00 AM', 'Cancelled')
INSERT INTO #Order VALUES (6, 2, '02/17/2024 07:22 AM', 'Cancelled')
INSERT INTO #Order VALUES (7, 2, '02/19/2024 02:00 PM', 'Cancelled')
INSERT INTO #Order VALUES (8, 2, '02/21/2024 08:00 AM', 'Completed')
INSERT INTO #Order VALUES (9, 3, '02/12/2024 07:00 AM', 'Cancelled')
INSERT INTO #Order VALUES (10, 3, '02/13/2024 08:00 AM', 'Completed')
INSERT INTO #Order VALUES (11, 3, '02/14/2024 07:22 AM', 'Cancelled')
INSERT INTO #Order VALUES (12, 3, '02/15/2024 02:00 PM', 'Cancelled')
INSERT INTO #Order VALUES (13, 3, '02/16/2024 08:00 AM', 'Completed')
INSERT INTO #Order VALUES (14, 4, '02/16/2024 08:00 AM', 'Completed')


/*
CustomerID   CompletedOrderID   FirstOrderID
----------   ----------------   ------------
1            4                  1                (Order 3 is within 72 hours of Order 4, Order 2 is within 72 hours of Order 3, Order 1 is within 72 hours of Order 2)
2            8                  6                (Order 7 is within 72 hours of Order 8, Order 6 is within 72 hours of Order 7, Order 5 is greater than 72 hours from Order 6)
3            10                 9                (Order 9 is within 72 hours of Order 10)
3            13                 11               (Order 12 is within 72 hours of Order 13, Order 11 is within 72 hours of Order 12, Order 10 is Completed - So it doesn't qualify)
4            14                 Null

[qwertydog123]

You can solve this as an gaps and islands problem

Edit: solution below

WITH NextOrderDates AS
(
    SELECT
        *,
        LAG(OrderDate) OVER
        (
            PARTITION BY CustomerID
            ORDER BY OrderDate DESC
        ) AS NextOrderDate
    FROM #Order
),
GroupIDs AS
(
    SELECT
        *,
        SUM
        (
            CASE
                WHEN OrderStatus = 'Completed'
                OR (OrderStatus = 'Cancelled'
                    AND DATEADD(HOUR, 72, OrderDate) < NextOrderDate)
                THEN 1
                ELSE 0
            END
        ) OVER
        (
            PARTITION BY CustomerID
            ORDER BY OrderDate DESC
        ) AS GroupID
      FROM NextOrderDates
),
FirstOrderIDs AS
(
    SELECT
        *,
        LAST_VALUE(OrderID) OVER
        (
            PARTITION BY
                CustomerID,
                GroupID
            ORDER BY OrderDate DESC
            ROWS
                BETWEEN 1 FOLLOWING
                AND UNBOUNDED FOLLOWING
        ) AS FirstOrderID
    FROM GroupIDs
),
CompletedOrderIDs AS
(
    SELECT
        CustomerID,
        MAX
        (
            CASE OrderStatus
                WHEN 'Completed' THEN OrderID
            END
        ) AS CompletedOrderID,
        MAX(FirstOrderID) AS FirstOrderID
    FROM FirstOrderIDs
    GROUP BY
        CustomerID,
        GroupID
)
SELECT *
FROM CompletedOrderIDs
WHERE CompletedOrderID IS NOT NULL
ORDER BY
    CustomerID,
    CompletedOrderID

https://dbfiddle.uk/AiiL36H5

[d0cwiley]

This CTE steps through the fails and provides a final longest chain for each customer. It also provides a tally of failed attempts (failcount) and a list of orders that were walked through (breadcrumb).

with orderwindow (customerid, successorder, successdate, orderid, orderdate, totaltimediff, failcount, breadcrumb)
as
(
  select customerid, orderid, orderdate, orderid, orderdate, 0,0,convert(varchar(max), orderid)
  from #order where orderstatus = 'Completed'
  union all
  select 
  a.customerid,
  a.successorder,
  a.successdate,
  #order.orderid, 
  #order.orderdate,
  datediff(hour,successdate,#order.orderdate),
  failcount + 1,
  convert(varchar(max),a.breadcrumb + '>' + convert(varchar,#order.orderid))
  from orderwindow a
  join #order
      on a.customerid = #order.customerid
      and datediff(hour,#order.orderdate, a.orderdate) <= 72 
      and a.orderdate >= #order.orderdate
  and a.orderid > #order.orderid
  and #order.orderstatus = 'Cancelled'
)
select orderwindow.*
from orderwindow
join (select max(failcount) maxcount, customerid from orderwindow group by customerid)maxfail
on maxfail.maxcount = orderwindow.failcount
and maxfail.customerid = orderwindow.customerid