January 22, 2009 at 10:36 pm
I've have a task that at first I thought was simple, and I'm more stuck than ever... I have a table that contains claims that get transferred from a primary to other entities. The table looks something like this:
ClaimNumber ParentClaimNumber
100 0
101 100
102 101
103 102
104 103
I want to associate each child claim to the root parent. I would desire the following result set from the table above:
ClaimNumber ParentClaimNumber InitialClaimNumber
100 0 100
101 100 100
102 101 100
103 102 100
104 103 100
I've heard that CTE would work for this, but I've read about it and just havn't connected the dots. Really what I am trying to do is group claims by the root claim number. Any help would be GREATLY appreciated!
January 22, 2009 at 11:17 pm
Hi
This CTE will give you the root parent claim number of a given claim number.
; WITH ClaimCTE (ClaimNumber)
AS
(
SELECT ClaimNumber FROM ClaimTable WHERE ClaimNumber = @Claimnumber
UNION ALL
SELECT C.ClaimNumber FROM ClaimTable C,ClaimCTE F
WHERE F.ClaimNumber = C.ParentClaimNumber
)
-- Insert into temp table if requried.
Select ClaimNumber From ClaimCTE
Store the restults in a temp table and use it for the next set of operations .
"Keep Trying"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply