Posted by Wes Groleau on 02/07/07 03:18

Erland Sommarskog wrote:
> Well, since you never care about posting your table defitions or sample
> data, do you really expect me to post a tested working query? Maybe you
> could at least post the actual query you tried, and the error message?

I'm not trying to be obscure--it's just that (1) I don't have access
to that system and Usenet at the same time and place and (2) the table
definitions were done with the GUI and (3) I couldn't get the scripting
to work. (I've done it before, but this time it asks for a filename and
then doesn't create the file)

> Again, can you post the actual error message, and the exact query you are

Here is what I thought I had already posted, but I have added more
explanation to it since then:
Load Raw_Segments

Raw_Segments
( FID int,
Seg_Nbr int,
SegType varchar(5),
Elem_01 varchar(x),
Elem_02 varchar(y), x, y, ..., z are between 10 and 50
...
Elem_nn varchar(z) )

FID, Seg_Nbr, SegType are in the same nonclustered index

Raw_Segments was loaded from one humongous file by DTS.

Create and insert Raw_BPR, Raw_CLP, Raw_SVC, Raw_CAS, Patient_Names, etc.

I forget how I created these but each has the same structure/definition
as Raw_Segments but a subset of the data. BPR, CLP, SVC, CAS are
SegTypes and Patient_Names is all the segments where SegType = 'NM1'
and Elem_01 =s 'QC'

Index the above the same as Raw_Segments

Create Inv_Data with indexes

From memory, probably
Inv_Data
( FID int, -- A
CAS_Seg int, -- B
Adj_Group varchar, -- C
Adj_Reason varchar, -- D
Adj_Amount money, -- E
SVC_Seg int, -- F
Service varchar, -- G
Name varchar, -- H
CLP_Seg int, -- I
Claim varchar, -- J
Status varchar, -- K
Charges money, -- L
BPR_Seg int, -- M
RA_Date date, -- N
Check_Amount money, -- O
Provider varchar ) -- P

A,B,D,F,H,I,J,M,P are indexed

Create View Selected_Adjustments from Raw_CAS

INSERT INTO Raw_CAS SELECT * FROM Raw_Segments WHERE SegType = 'CAS'

A CAS Segment can have up to six adjustments in different columns in the
same row so Selected_Adjustments is a view giving the union of
six selects to map them all into one set per row. Plus each has a
filter of Code in ('22', 'B22', '12', '50')

Raw_??? is like Raw_CAS


INSERT INTO Inv_Data -- which is empty until this happens
(FID, CAS_Seg, Adj_Group, Adj_Reason, Adj_Amount)
SELECT FID, Seg_Nbr, Type, Code, CAST(Amount AS money)
AS Expr1
FROM Selected_Adjustments -- this worked

-- Of the following updates, some worked and some timed out:
UPDATE Inv_Data
SET SVC_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND Inv_Data.CAS_Seg > Raw.Seg_Nbr)

UPDATE Inv_Data
SET Service = (SELECT Elem_01
FROM Raw_SVC AS Raw
WHERE Inv_Data.FID = Raw.FID
AND SVC_Seg = Seg_Nbr)

DELETE
FROM Inv_Data AS I
WHERE Adj_Reason = '50'
AND Service not like '%GA%'

UPDATE Inv_Data
SET CLP_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_CLP AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg > Seg_Nbr)

UPDATE Inv_Data
SET BPR_Seg = (SELECT MAX(Seg_Nbr)
FROM Raw_BPR AS Raw
WHERE Inv_Data.FID = Raw.FID
AND CAS_Seg > Seg_Nbr)

UPDATE Inv_Data
SET (fill in all the missing fields from Raw_whatever)

--
Wes Groleau

Change is inevitable. We need to learn that "inevitable" is
neither a synonym for "good" nor for "bad."
-- WWG

[Back to original message]