Hi
The following recursive option might work. It walks up and down from the queried invoice and joins the results.
DECLARE @invoiceID int = 3
;with rListUp as (
select a.invoiceid invoiceid, a.invoiceid currentid, a.bookingid, b.invoiceid nextinvoiceid
from #IDs a
CROSS APPLY (SELECT invoiceid, bookingid FROM #IDs m WHERE m.bookingid = a.bookingid ) b
where b.invoiceid > a.invoiceid
union all
select a.invoiceid, a.nextinvoiceid, a.bookingid, c.invoiceid
from rListUp a
CROSS APPLY (SELECT l.invoiceid, l.bookingid FROM #IDs l WHERE l.invoiceID = a.nextinvoiceid) b
CROSS APPLY (SELECT m.invoiceid, m.bookingid FROM #IDs m WHERE m.bookingid = b.bookingid and m.invoiceid > a.nextinvoiceid ) c
),
rListDown as (
select a.invoiceid invoiceid, a.invoiceid currentid, a.bookingid, b.invoiceid nextinvoiceid
from #IDs a
CROSS APPLY (SELECT invoiceid, bookingid FROM #IDs m WHERE m.bookingid = a.bookingid ) b
where b.invoiceid < a.invoiceid
union all
select a.invoiceid, a.nextinvoiceid, a.bookingid, c.invoiceid
from rListDown a
CROSS APPLY (SELECT l.invoiceid, l.bookingid FROM #IDs l WHERE l.invoiceID = a.nextinvoiceid) b
CROSS APPLY (SELECT m.invoiceid, m.bookingid FROM #IDs m WHERE m.bookingid = b.bookingid and m.invoiceid < a.nextinvoiceid ) c
),
rList AS (
SELECT invoiceid, nextinvoiceid FROM rListUp
UNION
SELECT invoiceid, nextinvoiceid FROM rListDown
)
select invoiceid, bookingid
from #IDs
where invoiceid in (
select nextinvoiceid from rlist where invoiceID = @InvoiceID
union
select @InvoiceID
)
It could probably be prettied up a bit more and there is still a chance that recursion limits will get hit if there is a long change of invoice/bookings