I have to write a SQL Stored Procedure by passing DOC Id as Input parameter and has to calculate the fee for each parent doc.
In my Document Table, I have Original Doc and Child Doc. For every doc, I pass as an Input parameter for my stored procedure, I have to find whether the Doc is a Child Doc or Parent Doc and if it is child, I have update the fee as $0.40.
Based on my example,
On passing AB12456 it has only one child and so billing is $0.40.
On passing CY12345, It has 3 child and one of its child became parent with two children. And one of its grandchild became parent and has 2 children. So this Parent Doc should be billed as $2.80.
Original Doc Child Doc Fee Expected Fee
AB12456 NX12450 $0.40
NY23422 NZ23422 $2.80
I have to write a logic without hard coding and calculate, how much each parent doc is billed. Amount has to be updated in the Fee Column.
How should i do this?