SEO

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.
*/

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.googlegr oups.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.
> */
>

Hi John,

I think that I may have been unclear about the problem. The Copy_To and
Copy_From columns are IDs. I should have probably put foreign keys to
My_ID in my example code to make that clear. Also, the Copy_To and
Copy_From tables are direct imports of files that I receive, so I have
no control over how that information is presented. If you had a way
that I could convert those rows into something like what you have
(instead of Claim and Part I would basically have the Orig_ID and
My_ID) then that would probably solve my problem.

Thanks,
-Tom.

Hi Thomas

This gives you the results you wanted, but if you have more than one chain
you will need a means of identifying them.

CREATE VIEW AllIds AS
SELECT My_Id FROM Chain
UNION SELECT My_Id FROM Copy_To
UNION SELECT My_Id FROM Copy_From

SELECT I.My_Id,
CASE WHEN I.My_Id = C.My_Id THEN C.Orig_ID ELSE C.My_Id END AS Orig_ID,
CASE WHEN I.My_Id = C.Copy_to THEN E.Copy_to ELSE (SELECT MIN(My_Id) FROM
AllIds P WHERE I.My_Id < P.My_Id) END AS Copy_To,
CASE WHEN I.My_Id = C.My_Id THEN C.Copy_From ELSE (SELECT MAX(My_Id) FROM
AllIds P WHERE I.My_Id > P.My_Id) END AS Copy_from
FROM AllIds I
JOIN Chain C ON C.My_Id <= I.My_Id AND C.Copy_to >= I.My_Id
JOIN Chain E ON C.Copy_to = E.My_Id AND E.Orig_id = C.My_id
ORDER BY I.My_Id

John


"Thomas R. Hummel" <tom_hummel@hotmail.com> wrote in message
news:1128138055.333646.242360@g14g2000cwa.googlegr oups.com...
> Hi John,
>
> I think that I may have been unclear about the problem. The Copy_To and
> Copy_From columns are IDs. I should have probably put foreign keys to
> My_ID in my example code to make that clear. Also, the Copy_To and
> Copy_From tables are direct imports of files that I receive, so I have
> no control over how that information is presented. If you had a way
> that I could convert those rows into something like what you have
> (instead of Claim and Part I would basically have the Orig_ID and
> My_ID) then that would probably solve my problem.
>
> Thanks,
> -Tom.
>

John Bell wrote:
> Hi Thomas
>
> This gives you the results you wanted, but if you have more than one chain
> you will need a means of identifying them.

Unfortunately, that's perhaps the biggest problem that I have. The only
way to determine that a row in Copy_To or Copy_From is in a chain with
another row is to follow the links. Once the rows are in the Chain
table the Orig_ID can be used to distinguish all chains.

I'll play around with the code that you included and see if I can get
something worked out. Unfortunately, I think that I'm going to be stuck
recursing through the chains to link them together.

Thanks for all of your help!

-Tom.

Hi

If your ranges do not overlap then you should be able to cope with the query
I posted use c.my_id as the chains identifier as your orig_id was NULL or
the first entry.

John


"Thomas R. Hummel" <tom_hummel@hotmail.com> wrote in message
news:1128177507.802681.287910@f14g2000cwb.googlegr oups.com...
> John Bell wrote:
>> Hi Thomas
>>
>> This gives you the results you wanted, but if you have more than one
>> chain
>> you will need a means of identifying them.
>
> Unfortunately, that's perhaps the biggest problem that I have. The only
> way to determine that a row in Copy_To or Copy_From is in a chain with
> another row is to follow the links. Once the rows are in the Chain
> table the Orig_ID can be used to distinguish all chains.
>
> I'll play around with the code that you included and see if I can get
> something worked out. Unfortunately, I think that I'm going to be stuck
> recursing through the chains to link them together.
>
> Thanks for all of your help!
>
> -Tom.
>

I've come up with a solution to the problem which I thought might be
useful for others. My problem was that I kept trying to find different
ways to force the data into the same structure. Finally it occurred to
me to look for a better model that is easier to work with.

I came up with the following:

CREATE TABLE Chain (
My_ID INT NOT NULL,
Orig_ID INT NOT NULL,
Level INT NOT NULL)
GO
ALTER TABLE Chain ADD CONSTRAINT PK_Chain PRIMARY KEY (My_ID)
GO

Now, converting the data is fairly quick and simple. First I add in all
of the "base" claims. Those are any claims that aren't copied from
somewhere else:

INSERT INTO Chain (My_ID, Orig_ID, Level)
SELECT CT1.My_ID, CT1.My_ID, 0
FROM Copy_To CT1
LEFT OUTER JOIN Copy_To CT2 ON CT2.Copy_To = CT1.My_ID
WHERE CT2.My_ID IS NULL

Once the base claims are added I add each new level in a loop until
there are no levels left:

DECLARE @ins_count INT
SET @ins_count = 1
WHILE (@ins_count > 0)
BEGIN
INSERT INTO Chain (My_ID, Orig_ID, Level)
SELECT
MIN(CF.My_ID),
C1.Orig_ID,
SQ.Max_Level + 1
FROM Copy_From CF
INNER JOIN Chain C1 ON C1.Orig_ID = CF.Copy_From
INNER JOIN (SELECT Orig_ID, MAX(Level) AS Max_Level
FROM Chain
GROUP BY Orig_ID) SQ ON SQ.Orig_ID = C1.Orig_ID
LEFT OUTER JOIN Chain C2 ON C2.My_ID = CF.My_ID
WHERE C2.My_ID IS NULL
GROUP BY C1.Orig_ID, SQ.Max_Level

SELECT @ins_count = @@ROWCOUNT
END

This logic handles all of the various problems with the data such as a
single claim being copied to multiple destination claims. The one
problem that it doesn't handle is a claim which is copied FROM multiple
claims, but this is an unresolvable error condition, so I find those
rows and move them to an error table ahead of time.

To improve performance I made a unique clustered index for Chain on the
Orig_ID and Level.

Getting the data that I need is fast and easy from this table as well:

SELECT C.My_ID, C.Orig_ID, CF.My_ID AS Copy_From, CT.My_ID AS Copy_To
FROM Chain C
LEFT OUTER JOIN Chain CF ON CF.Orig_ID = C.Orig_ID AND CF.Level =
C.Level - 1
LEFT OUTER JOIN Chain CT ON CT.Orig_ID = C.Orig_ID AND CT.Level =
C.Level + 1

-Tom.