Get all payment transactions recursively using SQL

I have a series of records. Based on the data below, how to get all transaction (XZ) for specific customer invoice (XR)?

Legend

XR - customer invoice
XZ - payments
XA - manual clearing

Example 1

Doc Nos      Type   TY    Amount     Cleared Doc
9500011864   A121   XR    36247.62   9000001660
9600009487   A121   XZ       -4.76   9000001660
9000001660   A121   XA    36242.86   9600012264
9600012264   A121   XZ   -72490.48   9600012264

Condition

Doc Nos : 9500011864
TY      : XR

Desired output

Doc Nos      Type   TY    Amount     Cleared Doc
9600009487   A121   XZ       -4.76   9000001660
9600012264   A121   XZ   -72490.48   9600012264

Explanation above: There is Customer invoice (XR) with the amount of 36,247.62. There is payment recorded (XZ) with the value of 4.76 for document nos (9500011864). Since this is partial payment, there is a manual clearing balance (XA) with the amount of 36,242.86. Another Payment is posted (XZ) with the amount of 72,490.48 referring to document nos (9000001660).

Example 2

Doc Nos      Type   TY    Amount     Cleared Doc
9500011864   A121   XR    36247.62   9000001660
9600009487   A121   XZ       -4.76   9000001660
9000001660   A121   XA    36242.86   9000001661
9000001661   A121   XZ       -2.86   9000001661
9000001661   A121   XA    36240.00   9600012264
9600012264   A121   XZ   -36240.00   9600012264

Condition

Doc Nos : 9500011864
TY      : XR

Desired output

Doc Nos      Type   TY    Amount     Cleared Doc
9600009487   A121   XZ       -4.76   9000001660
9000001661   A121   XZ       -2.86   9000001661
9600012264   A121   XZ   -36240.00   9600012264

Example 3

Doc Nos      Type   TY    Amount     Cleared Doc
9500000368   A121   XR    36247.62   9000000022
9000000022   A121   XA   -36247.62   9000000022
9000000022   A121   XA    36247.62   9000000022
9600016951   A121   XZ   -36247.62   9000000022

Condition

Doc Nos : 9500000368
TY      : XR

Desired output

Doc Nos      Type   TY    Amount     Cleared Doc
9600016951   A121   XZ   -36247.62   9000000022

How can I do this using SQL Server 2016? Is CTE Recursive applicable on this?

Answer

You need a recursive query. You select the XR record for the given “Doc Nos”. With its “Cleared Doc” you find related records (where “related” means they have this number in either “Doc Nos” or “Cleared Doc” depending on their TY). With all thus found records, you do the same with their “Cleared Doc”, etc. At last you show all XZ records from the found rows.

with cte(doc_nos, type, ty, amount, cleared_doc) as
(
  select doc_nos, type, ty, amount, cleared_doc
  from mytable 
  where doc_nos = 9500011864 and ty = 'XR'
  union all
  select t.doc_nos, t.type, t.ty, t.amount, t.cleared_doc
  from cte 
  join mytable t on (cte.ty = 'XR' and t.ty = 'XZ' and cte.cleared_doc = t.cleared_doc)
                 or (cte.ty = 'XR' and t.ty = 'XA' and cte.cleared_doc = t.doc_nos)
                 or (cte.ty = 'XA' and t.ty = 'XZ' and cte.cleared_doc = t.doc_nos)
)
select *
from cte
where ty = 'XZ';