We are using sql server 2012. I have a very complicated stored procedure. Final result set in a temp table will have all the transactions that are returned. This temp table has both parent and children records. The data shows up like this:
UniqueID fund txnID ParentID type status
1 100 10 9 78
2 100 11 10 64 Recon
3 100 12 10 64 Recon
Parents are always of type 78 and children 64. Children 2 and 3 belong to parent 1. There can be 3 children or 1 or 10 or 15. Children status are set already temp table based on certain conditions. My part is to set the status of parent based on children status. This is how children status can be.
If all children status is Recon, then parent status should be 'Recon'.
If all children status is 'Not settled', then parent status set to 'Not Settled'
If one of children status is 'Out of account', then parent should be marked as 'Out Of Account'.
If one or more children are 'Not settled' OR one or more 'PartiallyRecon' then, parent should be marked as 'Partially settled'.
If one or more children status is 'Mismatch', don't take these children status into consideration. Look for other status only.
How can I achieve this in Update Statement for parent.
I appreciate your response.