What's the best way to handle the following scenario in SQL Server 2005?
I have a table with claims in it that get transferred from one entity to the next, sometimes once, sometimes 10 times. The table has the following format. My goal is to group all the claims with their INITIAL claim (ParentClaimRef = 0). I can back track to the INITIAL claim using the ParentClaimRef, but am not sure how to write a query to do this automaticlly.
ClaimRef ParentClaimRef ClaimNumber
-------- -------------- -----------
35945 0 15517
35946 35945 15518
35947 35946 15519
Ideally, I'd like to end up with a result set that looks like this:
ClaimRef ParentClaimRef ClaimNumber InitialClaim
-------- -------------- ----------- ------------
35945 0 15517 15517
35946 35945 15518 15517
35947 35946 15519 15517
Any suggestions would be greatly appreciated! Thanks.
Matt
I have a table with claims in it that get transferred from one entity to the next, sometimes once, sometimes 10 times. The table has the following format. My goal is to group all the claims with their INITIAL claim (ParentClaimRef = 0). I can back track to the INITIAL claim using the ParentClaimRef, but am not sure how to write a query to do this automaticlly.
ClaimRef ParentClaimRef ClaimNumber
-------- -------------- -----------
35945 0 15517
35946 35945 15518
35947 35946 15519
Ideally, I'd like to end up with a result set that looks like this:
ClaimRef ParentClaimRef ClaimNumber InitialClaim
-------- -------------- ----------- ------------
35945 0 15517 15517
35946 35945 15518 15517
35947 35946 15519 15517
Any suggestions would be greatly appreciated! Thanks.
Matt
