Recurring key reference in a table.

Question

Post reply

Recurring key reference in a table.

Linehand

SSC Veteran

Points: 231
More actions
July 20, 2005 at 7:10 am

#186701

As a result of a data extract I have a table that holds Patient demographic details, one row for each time a patient has been in A&E. Each row has a unique key A&E number. The A&E number was been created for each A&E visit. It also holds the A&E number from the patient’s last visit. I would like to normalize the demographics and keep the A&E numbers in a cross reference table, since they are the foreign key to a number of tables holding clinical detail regarding the visit. Are there any T-SQL tricks anyone can suggest that would help? The table in question has 400K rows.

Illustration
A&E number    Full Name DOB         ……    Previous A&E number
P001 01/02/99 Joe Soap     16/02/69             P003 01/04/98
P003 01/04/98 Joe Soap     16/02/69              P075 01/05/97
P075 01/07/97 Joe Soap     16/02/69

What I would like is
Patient Demographics
            A&E number                Full Name        DOB                ……
            P001 01/02/99             Joe Soap          16/02/69

Cross –reference table

P001 01/02/99             P003 01/04/98
P001 01/02/99             P003 01/07/97

We have Microsoft SQL version 8.

Denise

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

Could you post the table definition and also a list of indexes on your table?

What is "Previous A & E number" for the first visit, null or blank?

And (just curious) what is "A & E"?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Seems to me that you might not have gone far enough in your normalisation quest. I suggest that you should have something like the following tables:

Patient(PatientID, Name, DOB, etc etc)

AandEVisits(VisitID, PatientID, Date)

With your data stored in this form, you can display the data the way you want. In particular, you should not store 'previous visit id' on the same row as 'visit id'. It might make your queries a little simpler, but the ongoing maintenance burden will outweigh this.

A&E stands for Accident and Emergency.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

Also - what is the A&E number data type and how is it generated ?!

It should be possible to extract the Patient information and insert into the Patient table that Phil outlines....(with grouping by patient name and taking the min of the date part of the A& E number) -

**ASCII stupid question, get a stupid ANSI !!!**

Linehand SSC Veteran Points: 231 More actions · Answer 4

Thank-you for the replies. Yes A&E number is Accident & Emergency. The data extract is fixed and the index is A&E number. First visit does have a null previous A&E number.

But I think you are right. Making a second table from the extract table and breaking up the date part of the number is on the right lines.

Thanks Denise

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 5

You are welcome

If you still need help, please post the table definition.