Posted by John Bell on 10/01/05 02:07

Hi

Your copy_to/from tables seems to be containing a hierarchy that you are
disregarding therefore they may be redundant!!!

If you had a master claimparts table then:

CREATE TABLE CLAIMPARTS ( claim char(1), part int )

INSERT INTO CLAIMPARTS ( claim , part )
SELECT 'A', 1
UNION ALL SELECT 'A', 2
UNION ALL SELECT 'A', 3
UNION ALL SELECT 'A', 4
UNION ALL SELECT 'B', 1
UNION ALL SELECT 'B', 8
UNION ALL SELECT 'B', 7
UNION ALL SELECT 'B', 3

SELECT C.CLAIM, C.PART,
(SELECT MIN(PART) FROM CLAIMPARTS P
WHERE C.PART < P.PART
AND C.CLAIM = P.CLAIM ) AS NEXTPART
FROM CLAIMPARTS C
ORDER BY C.CLAIM, C.PART

Would give you the hierarchy regardless of the previous copyto/from
information.

John

"Thomas R. Hummel" <tom_hummel@hotmail.com> wrote in message
news:1128105505.180521.155350@f14g2000cwb.googlegroups.com...
> Hello,
>
> I know that this has probably been discussed, but I couldn't find
> anything with Google. I have the following problem... I receive a set
> of text files that describe a chain of insurance claims. Each claim may
> be copied from an older claim and it may be copied to a newer claim. I
> get files that give information in both directions (i.e., One file says
> Claim #1 copied TO Claim #2 and another file says Claim #2 copied FROM
> Claim #1). I already have something in place to validate the files
> against each other.
>
> What I need to end up with is a row for each claim that gives the
> original claim number in the chain (NULL if it is an original claim),
> the claim from which it was copied (NULL if none), and the claim to
> which it was copied (NULL if none). The problem is that I may need to
> insert claims into the middle of a chain (ok, that's not hard), and I
> may also receive records in the file that have the same source claim
> but multiple destination claims. These records must be changed to
> create a single chain. For example, if I receive records that show 1->2
> and 1->3 then this should be converted to show 1->2 and 2->3. The
> business doesn't have any strong rules on how to reconcile these, so
> I've decided to just go with ordering by the claim IDs when determining
> order if it's otherwise unclear. Using this, I've solved the second
> situation as well, but then I started to consider the situation of
> receiving 1->2, 1->3, and 2->4. This should end up as 1->2, 2->3, 3->4
> but I can't figure out how to do that without cursors or multiple
> passes.
>
> Below is some SQL to work with. Any suggestions are greatly
> appreciated. Also, if you can think of any other situations that I
> might need to handle then that would be useful as well.
>
> Thanks,
> -Tom.
>
> CREATE TABLE Copy_To (
> My_ID INT NOT NULL,
> Copy_To INT NOT NULL )
> GO
> ALTER TABLE Copy_To ADD CONSTRAINT PK_Copy_To PRIMARY KEY (My_ID,
> Copy_To)
> GO
>
> CREATE TABLE Copy_From (
> My_ID INT NOT NULL,
> Copy_From INT NOT NULL )
> GO
> ALTER TABLE Copy_From ADD CONSTRAINT PK_Copy_From PRIMARY KEY (My_ID,
> Copy_From)
> GO
>
> CREATE TABLE Chain (
> My_ID INT NOT NULL,
> Orig_ID INT NULL,
> Copy_To INT NULL,
> Copy_From INT NULL )
> GO
> ALTER TABLE Chain ADD CONSTRAINT PK_Chain PRIMARY KEY (My_ID)
> GO
>
> INSERT INTO Copy_To VALUES (2, 3)
> INSERT INTO Copy_To VALUES (2, 4)
> INSERT INTO Copy_To VALUES (3, 5)
> GO
> INSERT INTO Copy_From VALUES (3, 2)
> INSERT INTO Copy_From VALUES (4, 2)
> INSERT INTO Copy_From VALUES (5, 3)
> GO
> -- The chain table may already have some existing rows
> INSERT INTO Chain VALUES (1, NULL, 6, NULL)
> INSERT INTO Chain VALUES (6, 1, NULL, 1)
> GO
> /*
> The expected results would be rows in Chain as follows:
>
> My_ID Orig_ID Copy_To Copy_From
> 1 NULL 2 NULL
> 2 1 3 1
> 3 1 4 2
> 4 1 5 3
> 5 1 6 4
> 6 1 NULL 5
>
> In this case the numbers are all sequential, but that will not always
> be the case. A chain could be 1->4->8 or possibly even 4->8->1->7.
> */
>

[Back to original message]