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
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
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
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
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
Thank you for response bitsmed. I didn't try your code. I will test and if that works for my scenario