What i came up with was to turn it into an adjacency list hierarchy and then we can recourse through that. of course i could be completly off base here but here is the code to turn it into the adjacency list
CREATE TABLE #IDsParents (
InvoiceId int not null
,BookingId int not null
,ParentInvoiceID INT
)
insert #IDsParents ( InvoiceId, BookingId)
select * from (values (1,9), (1,10), (1,11), (2,11), (3,11), (3,12), (3,13), (4,14), (5,14)) data(InvoiceId,BookingId)
;WITH Parent AS(
SELECT a.InvoiceId, b.InvoiceId AS Parent
FROM #IDsParents a
LEFT JOIN #IDsParents b
ON a.BookingId = b.BookingId
AND a.InvoiceId > b.InvoiceId
WHERE b.InvoiceId IS NOT NULL
)
UPDATE b SET ParentInvoiceID = a.Parent
FROM Parent a
RIGHT JOIN #IDsParents b
ON a.InvoiceId = b.InvoiceId
SELECT * FROM #IDsParents
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]