SQLTeam.com | Weblogs | Forums

Sql query to Update Parent status based on children status


#1

Hi,

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:
Table:
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.
Thank You
Spunny


#2

One way:

DECLARE @t TABLE (UniqueID INT, fund INT, txnID INT, ParentID INT, type INT, STATUS VARCHAR(10))

INSERT INTO @t (UniqueID, fund, txnID, ParentID, type, STATUS)
VALUES (1, 100, 10, 9, 78, ''), (2, 100, 11, 10, 64, 'Recon'), (3, 100, 12, 10, 64, 'Recon')

DECLARE @p TABLE (ParentID INT, STATUS VARCHAR(10))

INSERT INTO @p (ParentID)
VALUES (9), (10)

UPDATE p
SET p.STATUS = CASE 
          WHEN 'Recon' = ALL (
                    SELECT x.STATUS
                    FROM @t x
                    WHERE x.type = 64
                         AND x.ParentID = p.ParentID
                    )
               AND 1 <= (
                    SELECT count(*)
                    FROM @t x
                    WHERE x.type = 64
                         AND x.ParentID = p.ParentID
                         AND x.STATUS = 'Recon'
                    )
               THEN 'Recon'
          WHEN 'Not settled' <> ANY (
                    SELECT x.STATUS
                    FROM @t x
                    WHERE x.type = 64
                         AND x.ParentID = p.ParentID
                    )
               THEN 'Not settled'
          WHEN 1 = (
                    SELECT count(*)
                    FROM @t x
                    WHERE x.type = 64
                         AND x.ParentID = p.ParentID
                         AND x.STATUS = 'Out of account'
                    )
               THEN 'Out of account'
          WHEN 1 <= (
                    SELECT count(*)
                    FROM @t x
                    WHERE x.type = 64
                         AND x.ParentID = p.ParentID
                         AND x.STATUS IN ('Out of account', 'PartiallyRecon')
                    )
               THEN 'Partially settled'
          ELSE p.STATUS
          END
FROM @p p

SELECT *
FROM @p

#3

Does this mean: "If only one of child ..." or "If one or more children ..."?

In this query, I assume "If one ore more ...":

update t1
   set t1.status=t2.status
  from yourtable as t1
       inner join (select ParentID
                         ,case
                             when floor(avg(case when status='Recon' then 1.0 else 0.0 end))=1
                             then 'Recon'
                             when floor(avg(case when status='Not settled' then 1.0 else 0.0 end))=1
                             then 'Not Settled'
                             when sum(case when status='Out of account' then 1 else 0 end)>0
                             then 'Out Of Account'
                             when sum(case when status in ('Not settled','PartiallyRecon') then 1 else 0 end)>0
                             then 'Partially settled'
                          end as status
                     from yourtable
                    where type=64
                      and status<>'Mismatch'
                    group by ParentID
                  ) as t2
               on t2.ParentID=t1.txnID
 where t1.type=78

#4

Simpler:

WITH cte
AS (
     SELECT Parentid, count(*) AS Child_Count, count(CASE 
                    WHEN [status] = 'Recon'
                         THEN 1
                    END) AS Recon_Count, count(CASE 
                    WHEN [status] = 'Not settled'
                         THEN 1
                    END) AS NotSettled_Count, count(CASE 
                    WHEN [status] = 'Out of account'
                         THEN 1
                    END) AS OutOfAccount_Count, count(CASE 
                    WHEN [status] IN ('Out of account', 'PartiallyRecon')
                         THEN 1
                    END) AS OoA_or_ParialReconCount
     FROM @t
     WHERE type = 64
     GROUP BY ParentId
     )
UPDATE p
SET p.STATUS = CASE 
          WHEN Child_Count = Recon_Count
               THEN 'Recon'
          WHEN NotSettled_Count = 0
               THEN 'Not settled'
          WHEN OutOfAccount_Count = 1
               THEN 'Out Of Account'
          WHEN OoA_or_ParialReconCount >= 1
               THEN 'Partially settled'
          ELSE p.STATUS
          END
FROM @p p
INNER JOIN cte
     ON p.ParentID = cte.ParentID

#5

Thank you very much gbritton. Sorry for late reply. I was trying the code in my stored proc. It is working. I need to enhance it to rest of the statuses and test.

Thank You
Spunny


#6

Thank you for response bitsmed. I didn't try your code. I will test and if that works for my scenario