|
|
Posted by Thomas R. Hummel on 09/30/05 21:38
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.
*/
Navigation:
[Reply to this message]
|