Puzzling SQL

[tpoettker]

I have a very complicated SQL or at least it is to me the newbie.

I have 4 tables -- EVENT, ENTITY, PERSON_INVOLVED, GLOSSARY. The EVENT table is the main table. Each EVENT can have one or more PERSON_INVOLVED record. Each PERSON_INVOLVED record has one ENTITY record.

I need a view of the event, person_involved as patient, person_involved as visitor. Patient has the Pi_type_code of 238. Visitor has the pi_type_code of 240 AND glossary.system_table_name <> 'PHYSICIAN'

Here's the lastest SQL I have tried:

SELECT event.event_number, event.dttm_rcd_added,
EnPatient.first_name + ' ' + Enpatient.last_name AS PatientName,
EnVisitor.first_name + ' ' + Envisitor.last_name AS Visitor,
piVisitor.pi_type_code, system_table_name
FROM EVENT FULL OUTER JOIN
Person_involved AS PIPatient ON
(event.event_id = PIPatient.event_id AND
piPatient.pi_type_code = 238) FULL OUTER JOIN
entity AS EnPatient ON
piPatient.pi_eid = EnPatient.entity_id FULL OUTER JOIN
person_involved AS PIVisitor ON
(event.event_id = PiVisitor.event_id AND
PiVisitor.pi_type_code = 240) FULL OUTER JOIN
entity AS EnVisitor ON
PiVisitor.pi_eid = EnVisitor.entity_id FULL OUTER JOIN
glossary ON
(glossary.table_id = EnVisitor.entity_table_id AND
pivisitor.pi_type_code = 240 AND
glossary.system_table_name <> 'PHYSICIANS')
WHERE piPatient.pi_type_code = 238 AND
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112) OR
(piVisitor.pi_type_code = 240 AND
glossary.system_table_name <> 'PHYSICIANS') AND
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)

This SQL results in

EV088994 20040225023929 Joshua Harmon MattGoldstein 240
EV088995 20040225031156 SHARON DENNISON
EV088997 20040225031507 RUTH STUMPF
EV088999 20040225031801 ROBERT FREEMAN
EV089000 20040225031925 DONALD STANKUS
The problem is MattGoldstein 240 has the system_Table_name of PHYSICIAN. I have tried other SQL's but this is the closest to what I need.

Any ideas how to keep the Matts out of the results?

Thanks

 

[zhavic]

I know where the problem is, but I can't imagine it without sample data.

Can you post sample of data ( 10 rows ), with 'pi_type_code' codes ?

It needs to put FULL JOIN somewhere I thing, because you have in your data some events that have patient but not have visitor, and this condition filters them out:
AND ( PiVisitor.event_id IS NULL OR glossary.system_table_name <> 'PHYSICIANS' )

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[JonMa]

Your SQL is perfect.
As you say MattGoldstein 240 has the system_Table_name of PHYSICIAN. If you want him out you had to add him in the where-clause.

SELECT event.event_number, event.dttm_rcd_added,
EnPatient.first_name + ' ' + Enpatient.last_name AS PatientName,
EnVisitor.first_name + ' ' + Envisitor.last_name AS Visitor,
piVisitor.pi_type_code, system_table_name
FROM EVENT FULL OUTER JOIN
Person_involved AS PIPatient ON
(event.event_id = PIPatient.event_id AND
piPatient.pi_type_code = 238) FULL OUTER JOIN
entity AS EnPatient ON
piPatient.pi_eid = EnPatient.entity_id FULL OUTER JOIN
person_involved AS PIVisitor ON
(event.event_id = PiVisitor.event_id AND
PiVisitor.pi_type_code = 240) FULL OUTER JOIN
entity AS EnVisitor ON
PiVisitor.pi_eid = EnVisitor.entity_id FULL OUTER JOIN
glossary ON
(glossary.table_id = EnVisitor.entity_table_id AND
pivisitor.pi_type_code = 240 AND
glossary.system_table_name <> 'PHYSICIANS')
WHERE piPatient.pi_type_code = 238 AND
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112) OR
(piVisitor.pi_type_code = 240 AND
glossary.system_table_name <> 'PHYSICIANS') AND
glossary.system_table_name <> 'PHYSICIAN') AND
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)

 

[tpoettker]

Here are a small version of the tables. These will cover the basics of my SQL



Create table event
(event_id int,
event_number char(25),
dttm_rcd_added char(14))


create table person_involved
(event_id int,
pi_eid int,
pi_type_code int)

create table entity
(entity_id int,
entity_table_id int,
last_name char(50),
first_name char(50))

create table glossary
(table_id int,
system_table_name char(18)0


Sample data is as follows:


Event Table
event_id event_number dttm_rcd_added
89419,EV089419,20040301033636
89424,EV089424,20040301085352
89425,EV089425,20040301100432
89431,EV089431,20040301103117
89432,EV089432,20040301103356
89435,EV089435,20040301104049
89438,EV089438,20040301105215
89442,EV089442,20040301110223
89445,EV089445,20040301111117
89456,EV089456,20040301130651
89457,EV089457,20040301130958
89461,EV089461,20040301134401
89463,EV089463,20040301140526
89478,EV089478,20040301150629
89481,EV089481,20040301152324
89485,EV089485,20040301155208
89486,EV089486,20040301162340
89488,EV089488,20040301165743
89489,EV089489,20040301181007


Person_involved table
event_id pi_eid pi_type_code
89420,222160,238
89420,222161,240
89420,222162,239
89421,222164,238
89421,222165,240
89422,222167,238
89427,222180,238
89428,222182,238
89429,222184,238
89430,222186,238
89434,222196,238
89437,222202,238
89439,222206,238
89419,222157,238
89419,222158,239
89424,222172,240
89424,222173,239
89425,222175 238
89425,222176 240

Entity Table
entity_id entity_table_id last_name first_name
222157,1061,greene,drucillia
222158,1063,telemetry,monitor
222172,1046,Jones,Kim
222173,1063,Anderson,Ravern
222175,1061,roberts,mark
222176,1086,Polinsky,
222190,1061,lomax,henrietta
222191,1086,svrakic,dragon
222208,1061,Polaski,Mary E.
222210,1061,Reed,William
222215,1061,smith,latacia
222217,1061,brown,linda
222221,1061,Finch,Linda
222224,1061,Lewis,Deborah
222225,1086,Reddy,Agara
222228,1061,murray,robbin
222232,1061,Safety Concern,Unknown
222234,1061,hickinbotham,letha
222240,1061,Safety Concern,Unknown
222242,1061,kuntzman,louis
222188,1061,Cavaness,Frederick
222198,1061,Hartman,Harold
222204,1061,wieda,benjamin
222212,1046,loonstein,jack
222213,1063,McCoy,Barb
222219,1061,Sykes,Beatrice
222246,1061,Safety Concern,Unknown
222248,1061,Safety Concern,Unknown

table_id system_table_name
1086,PHYSICIANS
1063,WITNESS
1584,PATIENT
1012,DEPARTMENT
1046,OTHER_PEOPLE
1061,PATIENTS

 

[JonMa]

Hello again.
I've tried to rewrite some of your code, please try.

SELECT event.event_number,
event.dttm_rcd_added,
PI.pi_type_code,
entity.first_name + ' ' + entity.last_name,
system_table_name
FROM EVENT
LEFT OUTER JOIN
Person_involved AS PI ON (event.event_id = PI.event_id AND (PI.pi_type_code = 238 OR PI.pi_type_code = 240))
LEFT OUTER JOIN
entity ON PI.pi_eid = entity.entity_id
FULL OUTER JOIN
glossary ON (glossary.table_id = entity.entity_table_id AND PI.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS')
WHERE PI.pi_type_code = 238 AND SUBSTRING(event.dttm_rcd_added, 1, 8) >= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)
OR (PI.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS') AND SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(26), DATEADD(dd, - 1, GETDATE()), 112)

 

[tpoettker]

this code only gave me patients and not visitors. However, I do finally have the solution. It is:

SELECT e.Event_Number, e.DtTm_Rcd_Added, ep.First_Name + ' ' + ep.Last_Name PatientName,
ev.First_Name + ' ' + ev.Last_Name Vistor, v.Pi_Type_Code, g.System_Table_Name
FROM Event e
JOIN Person_Involved p ON e.Event_Id = p.Event_Id AND p.Pi_Type_Code = 238
JOIN Entity ep ON p.Pi_Eid = ep.Entity_Id
LEFT JOIN Person_Involved v ON e.Event_Id = v.Event_Id AND v.Pi_Type_Code = 240
LEFT JOIN Entity ev ON v.Pi_Eid = ev.Entity_Id
LEFT JOIN Glossary g ON ev.Entity_Table_Id = g.Table_Id AND g.System_Table_Name <> 'PHYSICIANS'
WHERE LEFT(e.DtTm_Rcd_Added,8) >= CONVERT(char(8),GETDATE() - 1,112)

Thansk for all the helped. I appreciate it!!

 

[JonMa]

Good you solved it. I just can't understand why the code below should not work (letting Person_involved and Event appear only once). Maybe I'm lacking som knowledge.

SELECT e.Event_Number, e.DtTm_Rcd_Added, ep.First_Name + ' ' + ep.Last_Name PatientName,
v.Pi_Type_Code, g.System_Table_Name
FROM Event e
JOIN Person_Involved p ON e.Event_Id = p.Event_Id AND (p.Pi_Type_Code = 238 OR v.Pi_Type_Code = 240)
JOIN Entity ep ON p.Pi_Eid = ep.Entity_Id
LEFT JOIN Glossary g ON ep.Entity_Table_Id = g.Table_Id AND g.System_Table_Name <> 'PHYSICIANS'
WHERE LEFT(e.DtTm_Rcd_Added,8) >= CONVERT(char(8),GETDATE() - 1,112)