Stepping Back Through Data
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).
Also, the logic should stop if it finds another "Completed" order for the same customer.
I've included a test table and data, as well as an expected output.
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