Puzzling SQL
-
Tina, can you post some sample data?
* Noel -
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
-
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
WHERE LEFT(e.DtTm_Rcd_Added,8) >= CONVERT(char(8),GETDATE() - 1,112)
AND (g.System_Table_Name <> 'PHYSICIANS' OR g.System_Table_Name IS NULL)
Rather amusing to use the bad practice of Hungarian naming of column identifiers and then not follow that...
--Jonathan -
Jonathan,
Thank you. It has been a long, hard 3 weeks trying to get this to work. It looks like your code did it.
Thank you again.
Tina
-
Tina,
I tried Jonathan last code and my code:
SELECT
event.event_number
, event.dttm_rcd_added
, Patients.PatientName
, Visitors.VisitorName
, system_table_name
FROM
Event
JOIN
(SELECT event_id, Entity.first_name + ' ' + Entity.last_name AS PatientName
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
WHERE Person_involved.pi_type_code = 238) Patients
ON Event.event_id = Patients.event_id
LEFT OUTER JOIN
(SELECT Person_involved.event_id , Entity.first_name + ' ' + Entity.last_name AS VisitorName, system_table_name
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
JOIN
glossary
ON Glossary.table_id = Entity.entity_table_id
WHERE Person_involved.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS') AS Visitors
ON Event.event_id = Visitors.event_id
WHERE
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(8), DATEADD(dd, - 1, GETDATE()), 112)
And I had the same results than Jonathan's. I was joining by the wrong filed.
oh well, now you have two versions
* Noel -
You're welcome. Notice how much easier it is to find a solution once you provide some hard information.
Actually, you may want this instead:
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)
As you were using outer joins so liberally, perhaps you want all patients, even if the only visitors they had were physicians.
I'm worried about the schema design. The data types seem misguided, and columns with names like "Table_Id" and "System_Table_Name" imply that this may be violating Codd's Information Rule.
--Jonathan -
I had to Move some data around just to have intersecting sets because the data was not overlapping very well like:
if exists (select * from dbo.sysobjects where id = object_id(N'[event]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [event]
GO
Create table event
(event_id int,
event_number char(25),
dttm_rcd_added char(14))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[person_involved]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [person_involved]
GO
create table person_involved
(event_id int,
pi_eid int,
pi_type_code int)
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[entity]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [entity]
GO
create table entity
(entity_id int,
entity_table_id int,
last_name char(50),
first_name char(50))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[glossary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [glossary]
GO
create table glossary
(table_id int,
system_table_name char(18))
GO
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89420,'EV089419', '20040301033636')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89420,'EV089424', '20040301085352')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89420,'EV089425', '20040301100432')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89421,'EV089431', '20040301103117')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89421,'EV089432', '20040301103356')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89422,'EV089435', '20040301104049')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89427,'EV089438', '20040301105215')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89428,'EV089442', '20040301110223')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89429,'EV089445', '20040301111117')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89430,'EV089456', '20040301130651')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89434,'EV089457', '20040301130958')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89437,'EV089461', '20040301134401')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89439,'EV089463', '20040301140526')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89419,'EV089478', '20040301150629')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89419,'EV089481', '20040301152324')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89424,'EV089485', '20040301155208')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89424,'EV089486', '20040301162340')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89425,'EV089488', '20040301165743')
INSERT INTO Event(event_id, event_number, dttm_rcd_added) VALUES(89425,'EV089489', '20040301181007')
--Person_involved
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89420,222160,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89420,222161,240)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89420,222162,239)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89421,222164,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89421,222165,240)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89422,222167,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89427,222180,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89428,222182,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89429,222184,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89430,222186,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89434,222196,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89437,222202,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89439,222206,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89419,222157,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89419,222158,239)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89424,222172,240)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89424,222173,239)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89425,222175,238)
INSERT INTO Person_involved(event_id, pi_eid, pi_type_code) VALUES(89425,222176,240)
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222160,1061,'greene', 'drucillia')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222161,1063,'telemetry', 'monitor')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222162,1046,'Jones', 'Kim')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222164,1063,'Anderson', 'Ravern')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222165,1061,'roberts', 'mark')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222167,1086,'Polinsky', '')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222180,1061,'lomax', 'henrietta')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222182,1086,'svrakic', 'dragon')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222184,1061,'Polaski', 'Mary E.')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222186,1061,'Reed', 'William')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222196,1061,'smith', 'latacia')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222202,1061,'brown', 'linda')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222206,1061,'Finch', 'Linda')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222157,1061,'Lewis', 'Deborah')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222158,1086,'Reddy', 'Agara')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222172,1061,'murray', 'robbin')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222173,1061,'Safety Concern', 'Unknown')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222175,1061,'hickinbotham', 'letha')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222176,1061,'Safety Concern', 'Unknown')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222242,1061,'kuntzman', 'louis')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222188,1061,'Cavaness', 'Frederick')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222198,1061,'Hartman', 'Harold')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222204,1061,'wieda', 'benjamin')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222212,1046,'loonstein', 'jack')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222213,1063,'McCoy', 'Barb')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222219,1061,'Sykes', 'Beatrice')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222246,1061,'Safety Concern', 'Unknown')
INSERT INTO Entity(entity_id, entity_table_id, last_name, first_name) VALUES (222248,1061,'Safety Concern', 'Unknown')
INSERT INTO dbo.glossary(table_id, system_table_name) VALUES (1086,'PHYSICIANS')
INSERT INTO dbo.glossary(table_id, system_table_name) VALUES (1063,'WITNESS')
INSERT INTO dbo.glossary(table_id, system_table_name) VALUES (1584,'PATIENT')
INSERT INTO dbo.glossary(table_id, system_table_name) VALUES (1012,'DEPARTMENT')
INSERT INTO dbo.glossary(table_id, system_table_name) VALUES (1046,'OTHER_PEOPLE')
INSERT INTO dbo.glossary(table_id, system_table_name) VALUES (1061,'PATIENTS')
SELECT
event.event_number
, event.dttm_rcd_added
, Patients.PatientName
, Visitors.VisitorName
, system_table_name
FROM
Event
JOIN
(SELECT event_id, RTRIM(Entity.first_name) + ' ' + RTRIM(Entity.last_name) AS PatientName
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
WHERE Person_involved.pi_type_code = 238) Patients
ON Event.event_id = Patients.event_id
LEFT OUTER JOIN
(SELECT Person_involved.event_id , RTRIM(Entity.first_name) + ' ' + RTRIM(Entity.last_name) AS VisitorName, system_table_name
FROM Person_involved
JOIN
Entity
ON Entity.entity_id = Person_involved.pi_eid
JOIN
glossary
ON Glossary.table_id = Entity.entity_table_id
WHERE Person_involved.pi_type_code = 240 AND glossary.system_table_name <> 'PHYSICIANS') AS Visitors
ON Event.event_id = Visitors.event_id
WHERE
SUBSTRING(event.dttm_rcd_added, 1, 8)
>= CONVERT(char(8), DATEADD(dd, - 1, GETDATE()), 112)
ORDER BY 1 --For Comparison
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
WHERE LEFT(e.DtTm_Rcd_Added,8) >= CONVERT(char(8),GETDATE() - 1,112)
AND (g.System_Table_Name <> 'PHYSICIANS' OR g.System_Table_Name IS NULL)
ORDER BY 1 --For Comparison
I also will recommend you change the char(50) on Entity for varchar(50) it will make your life easier
I also compare Jonathan's version against Mine (Just on Query Cost)
and Jonathan's was better 
This was really fun
* Noel -
unfortunately, I have no control over the data schema. I inherited this database and am in not in an authority to make any changes. I do, however, agree with you.
Thanks to all for all the help.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply