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?
Advertisement
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';