Update parent status based on all children status and sum of children amount.

Hi Spunny,



Thanks for the clarification, the scenarios listed do help to comprehend the logic.



For this case, since the status is enumerable, I would suggest to use a auxilliary table as below.

CREATE TABLE [Status]
(
ID INT,
Status VARCHAR(99)
)

INSERT INTO [Status] VALUES
(1,'Reconciled'),  --you should assign the id sequence based on the status priority
(2,'Partially Reconciled'),
(3,'Settled'),
(4,'Partially Settled'),
(5,'Mismatched')

You can join the [Status] table and aggregate grouping by parent. See the pseudo code as below.

;WITH cte AS(
SELECT ParentID,CASE WHEN SUM(childAmount)=MAX(parentAmount) THEN MAX(s.ID) ELSE 5 END AS Status_ID FROM ParentChildren p JOIN [Status] s ON p.childStatus = s.Status
GROUP BY ParentID 
)
SELECT * FROM cte c JOIN [Status] s ON C.Status_ID=s.ID

If you have any feedback on our support, you can click here.

---

Eric Zhang

TechNet Community Support