MSSQL finding originator batch - without cursor

Question

i'm looking for some help on the following on SQL2016

I have batches of which i need to identifying the parent batch.

I'd need to fill is ParentBatchID and should work according to this logic:

1. If there exists a previous batch where the current batch's From date is between the previous batch's From and to -> Thats the parrent
2. If the identified parrent's parrentBatchID is 0, then get the parrent's BatchID, if it's not 0, then get the parrent's ParrentBatchID (inherit it)

So far i did this with a cursor, but the performance is, well you know, RBAR. *when current fromDate is between previous from and to date+1 then isnull(parrentBatchID,batchid) *

I tried with window functions, but as the parrentID i need to inherit does not exist yet in the next line i failed and just got 0-s everywhere :(

I have free manipulation over the data and tried with various prepare approaches also.

Create table myTest (BatchID int, Product int, LenghtDay int, fromDate datetime, toDate Datetime, ParentBatchID int);

insert into myTest VALUES (1,1,9,'2000-01-01 00:00:00','2000-01-10 00:00:00',null);
insert into myTest VALUES (2,1,4,'2000-01-08 00:00:00','2000-01-12 00:00:00',null);
insert into myTest VALUES (3,1,5,'2000-01-13 00:00:00','2000-01-18 00:00:00',null);
insert into myTest VALUES (4,1,2,'2000-01-20 00:00:00','2000-01-22 00:00:00',null);
insert into myTest VALUES (5,2,7,'2000-01-21 00:00:00','2000-01-28 00:00:00',null);

Output would be expected

BatchID,ProductID,ParentBatchID 
1,1,0
2,1,1 --as it's from (01.08) is between #1's from (01.01) and to+1 (01.11)
3,1,1 --as it's from (01.13) is between #2's from (01.08) and to+1 (01.13), so it needs to inherit #2's parent batch ID: 1
4,1,0 --as it's not between #3's from and to, it's a new batch
5,2,0 --as it's a different product, different batch

---------- There is an extended problem version, where if i identify the parrent i need to extend it's length with the child's length day, and i need to check the next child if it's from is in the the from -> to + previous childern's length. To be frank i have no idea how to implement this. ---------- Extended version's - line #4 will be different

BatchID,ProductID,ParentBatchID 
1,1,0
2,1,1 --as it's from (01.08) is between #1's from (01.01) and to+1 (01.11)
3,1,1 --as it's from (01.13) is between #1's from (01.01) and to+1+#2's length (01.10+1+4=016), so it needs to inherit #2's parent batch ID: 1
4,1,1 --! ParentBatchID=1 ! as it's from (01.20) is between the from (01.01) and to+1+extended by all previous children's length (01.10+1+4+5=01.20), so it needs to inherit #3's parent batch ID: 1  
5,2,0 --as it's a different product, different batch

All guidance and help is welcome Thank you in advance

Nikla

---

Edit

The recursion solution is great, but only runs for the most limited of datasets. Can you suggest a more scalable approach?

Thank you

Answer 1

;with t as 
(
  select tt.BatchID, tt.Product, tt.fromDate, tt.toDate, 0 ParentBatchID, 1 lvl, tt.BatchID rootID
  from myTest tt
  where not exists(select 1 from myTest t 
                   where t.Product=tt.Product 
                     and tt.BatchID <> t.BatchID
                     and tt.fromDate between t.fromDate and dateadd(day, 1, t.toDate)
                  )

  union all

  select tt.BatchID, tt.Product, tt.fromDate, tt.toDate, t.BatchID, t.lvl+1, t.rootID
  from t
  INNER JOIN myTest tt
  on tt.Product = t.Product
  where tt.BatchID <> t.BatchID 
    and tt.fromDate between t.fromDate and dateadd(day, 1, t.toDate)
)
select BatchId, Product, ParentBatchID, rootId, lvl
from t
order by Product, fromDate, lvl

http://sqlfiddle.com/#!18/971f3/37

BatchId Product ParentBatchID   rootId  lvl
1   1   0   1   1
2   1   1   1   2
3   1   2   1   3
4   1   0   4   1
5   2   0   5   1