Avatar of Jeff S
Jeff S
Flag for United States of America asked on

SQL Query help needed ASAP.

I need some help with my below mentioned SQL Query. Per my clients request, they ONLY want to print the PatientContact info when the relationship is either "Guardian" or "LAS".

Here is the twist. A patient can have many contacts ... they can even have one maked with "Guardian" and one marked with "LAS". The great part is if they have one maked "Guardian" and one marked "LAS", they only want the one marked "Guardian" and omit the others. If they do not have "Guardian" and only have "LAS" they want that one and kill off the others. If a patient does not have either "Guardian" or "LAS" they want the Contact First Name to read as : "No Guardian or LAS Listed in Contacts".

Basically, they want "Guardian" first ... if no "Guardian" they will take "LAS" ... in neither just print that comment.


SET NOCOUNT ON 

SELECT
    pp.PatientProfileId ,
    dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName , 
    ISNULL(ml.Description ,'') AS Relationship , 
    ISNULL(pc.First,'') AS [Contact First] ,
    ISNULL(pc.Middle,'') AS [Contact Middle] ,
    ISNULL(pc.Last,'') AS [Contact Last], 
    ISNULL(pc.Address1,'') AS [Contact Addr1],
    ISNULL(pc.Address2,'') AS [Contact Addr2],
    ISNULL(pc.City,'') AS [Contact City],
    ISNULL(pc.State,'') AS [Contact State],
    ISNULL(pc.Zip,'') AS [Contact Zip],
    pr.ListOrder
FROM
    PatientProfile pp
    LEFT JOIN PatientRelationship pr ON pp.PatientProfileId = pr.PatientProfileId AND pr.Type = 5
    LEFT JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
    LEFT JOIN MedLists ml ON pr.RelationshipTypeMId = ml.MedListsId
    
ORDER BY 2, 12

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Jeff S

8/22/2022 - Mon
Dale Burrell

There might be a neater solution but I recon this will do it.

SELECT
  pp.PatientProfileId
  , dbo.FormatName(pp.Prefix , pp.[First] , pp.Middle , pp.[Last] , pp.Suffix) PatientName 
  , ISNULL(isnull(ml.[Description],ml1.[Description]),'') Relationship
  , ISNULL(isnull(pc.[First],pc1.[First]),'No Guardian or LAS Listed in Contacts') [Contact First]
  , ISNULL(isnull(pc.Middle,pc1.Middle),'') [Contact Middle]
  , ISNULL(isnull(pc.[Last],pc1.[Last]),'') [Contact Last]
  , ISNULL(isnull(pc.Address1,pc1.Address1),'') [Contact Addr1]
  , ISNULL(isnull(pc.Address2,pc1.Address2),'') [Contact Addr2]
  , ISNULL(isnull(pc.City,pc1.City),'') [Contact City]
  , ISNULL(isnull(pc.[State],pc1.[State]),'') [Contact State]
  , ISNULL(isnull(pc.Zip,pc1.Zip),'') [Contact Zip]
  , isnull(pr.ListOrder,pr1.ListOrder)
FROM PatientProfile pp
-- Join on a contact if its of type Guardian
LEFT JOIN PatientRelationship pr ON pp.PatientProfileId = pr.PatientProfileId AND pr.[Type] = 5
LEFT JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
LEFT JOIN MedLists ml ON pr.RelationshipTypeMId = ml.MedListsId and ml.[Description] = 'Guardian'
-- Join on a contact if its of type LAS
LEFT JOIN PatientRelationship pr1 ON pp.PatientProfileId = pr1.PatientProfileId AND pr1.[Type] = 5
LEFT JOIN PatientContacts pc1 ON pr.RelatedPartyId = pc1.PatientContactsId
LEFT JOIN MedLists ml1 ON pr1.RelationshipTypeMId = ml1.MedListsId and ml.[Description] = 'LAS'
ORDER BY 2, 12

Open in new window

Bhavesh Shah

they have only two types of relationship?

Guardian or LAS?
Bhavesh Shah

if so then

this should also work.

SELECT
    pp.PatientProfileId ,
    dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName , 
    ISNULL(ml.Description ,'No Guardian') AS Relationship , 
    ISNULL(pc.First,'') AS [Contact First] ,
    ISNULL(pc.Middle,'') AS [Contact Middle] ,
    ISNULL(pc.Last,'') AS [Contact Last], 
    ISNULL(pc.Address1,'') AS [Contact Addr1],
    ISNULL(pc.Address2,'') AS [Contact Addr2],
    ISNULL(pc.City,'') AS [Contact City],
    ISNULL(pc.State,'') AS [Contact State],
    ISNULL(pc.Zip,'') AS [Contact Zip],
    pr.ListOrder
FROM
    PatientProfile pp
    LEFT JOIN PatientRelationship pr ON pp.PatientProfileId = pr.PatientProfileId AND pr.Type = 5
    LEFT JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
    LEFT JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Description) AS Row, M.*
			   FROM		
					MedLists m
				)m1
	ON pr.RelationshipTypeMId = ml.MedListsId
	AND M1.Row = 1
    
ORDER BY 2, 12

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jeff S

ASKER
There are more then just those two relationships. They are just interested in those two.
Bhavesh Shah

try this.

SELECT
    pp.PatientProfileId ,
    dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName , 
    ISNULL(ml.Description ,'No Guardian') AS Relationship , 
    ISNULL(pc.First,'') AS [Contact First] ,
    ISNULL(pc.Middle,'') AS [Contact Middle] ,
    ISNULL(pc.Last,'') AS [Contact Last], 
    ISNULL(pc.Address1,'') AS [Contact Addr1],
    ISNULL(pc.Address2,'') AS [Contact Addr2],
    ISNULL(pc.City,'') AS [Contact City],
    ISNULL(pc.State,'') AS [Contact State],
    ISNULL(pc.Zip,'') AS [Contact Zip],
    pr.ListOrder
FROM
    PatientProfile pp
    LEFT JOIN PatientRelationship pr ON pp.PatientProfileId = pr.PatientProfileId AND pr.Type = 5
    LEFT JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
    LEFT JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Description) AS Row, M.*
			   FROM		
					MedLists m
				WHERE 	Description IN ('GUARDIAN','LAS')
				)m1
	ON pr.RelationshipTypeMId = ml.MedListsId
	AND M1.Row = 1
    
ORDER BY 2, 12

Open in new window

Jeff S

ASKER
This is much closer to where it has been. Only minor issue I see right now is I want only one row per patient and right now I am getting back rows for every instance of the contact.

Example: On one of my test patients, i have two contacts - one for "Guardian" and one for something else - "Sister". The data set is returning the two rows for this patient. I only need one row per patient and for it to follow the follow down list they gave me.

"Guardian" always first.
"LAS" - secondary to "Guardian"; however if no contact equals "Guardian", pull "LAS"
If neither "Guardian" or "LAS" are available I need to return the message "No Guardian or LAS in Contacts".

This seems to be very very close, just need to kill off the other ones in the file for the patient and only return the one.


SET NOCOUNT ON 

SELECT --TOP 1
    pp.PatientProfileId ,
    dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName , 
    ISNULL(ml.Description ,'No Guardian or LAS in Contacts') AS Relationship , 
    ISNULL(pc.First,'') AS [Contact First] ,
    ISNULL(pc.Middle,'') AS [Contact Middle] ,
    ISNULL(pc.Last,'') AS [Contact Last], 
    ISNULL(pc.Address1,'') AS [Contact Addr1],
    ISNULL(pc.Address2,'') AS [Contact Addr2],
    ISNULL(pc.City,'') AS [Contact City],
    ISNULL(pc.State,'') AS [Contact State],
    ISNULL(pc.Zip,'') AS [Contact Zip],
    pr.ListOrder, 
    pr.Created

FROM
    PatientProfile pp
    LEFT JOIN PatientRelationship pr ON pp.PatientProfileId = pr.PatientProfileId AND pr.Type = 5
    LEFT JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
    LEFT JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Description) AS Row, M.*
			   FROM		
					MedLists m
				WHERE 	Description IN ('GUARDIAN','LAS')
				)ml
	ON pr.RelationshipTypeMId = ml.MedListsId
	AND Ml.Row = 1
    
ORDER BY 2, 12

Open in new window

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Bhavesh Shah

what will be RelationshipTypeMId when there is no data in medlists
Bhavesh Shah

SELECT --TOP 1
    pp.PatientProfileId ,
    dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName , 
    ml.Relationship , 
    ISNULL(pc.First,'') AS [Contact First] ,
    ISNULL(pc.Middle,'') AS [Contact Middle] ,
    ISNULL(pc.Last,'') AS [Contact Last], 
    ISNULL(pc.Address1,'') AS [Contact Addr1],
    ISNULL(pc.Address2,'') AS [Contact Addr2],
    ISNULL(pc.City,'') AS [Contact City],
    ISNULL(pc.State,'') AS [Contact State],
    ISNULL(pc.Zip,'') AS [Contact Zip],
    pr.ListOrder, 
    pr.Created

FROM
    PatientProfile pp
    LEFT JOIN PatientRelationship pr ON pp.PatientProfileId = pr.PatientProfileId AND pr.Type = 5
    LEFT JOIN PatientContacts pc ON pr.RelatedPartyId = pc.PatientContactsId
    INNER JOIN (SELECT 1 AS Row,-1 MedListsId,'No Guardian or LAS in Contacts'Relationship
				UNION
				SELECT ROW_NUMBER() OVER(ORDER BY Description) AS Row, MedListsId, Description
			    FROM		
					MedLists m
				WHERE 	Description IN ('GUARDIAN','LAS')
				)ml
	ON pr.RelationshipTypeMId = ml.MedListsId
	AND Ml.Row = 1

ORDER BY 2, 12

 

Open in new window



Here put the RelationshipTypeMId value in place of "-1".
RelationshipTypeMId = where there is no data in medlist.
Jeff S

ASKER
NULL. A patient can have no contacts listed. If so that would fall under "No Guardian or LAS Listed in Contacts". I have been playing with this and it seems much closer, however I am still getting a second record on my test patient when I have "Guardian" and "LAS" contacts on same patient. I only need one record per patient.

SELECT	--TOP 1
		pp.PatientProfileId ,
		dbo.FormatName(pp.Prefix , pp.First , pp.Middle , pp.Last , pp.Suffix) AS PatientName , 
		ISNULL(FirstPatient.Description ,'No Guardian or LAS in Contacts') AS Relationship , 
		ISNULL(FirstPatient.First,'') AS [Contact First] ,
		ISNULL(FirstPatient.Middle,'') AS [Contact Middle] ,
		ISNULL(FirstPatient.Last,'') AS [Contact Last], 
		ISNULL(FirstPatient.Address1,'') AS [Contact Addr1],
		ISNULL(FirstPatient.Address2,'') AS [Contact Addr2],
		ISNULL(FirstPatient.City,'') AS [Contact City],
		ISNULL(FirstPatient.State,'') AS [Contact State],
		ISNULL(FirstPatient.Zip,'') AS [Contact Zip],
		FirstPatient.ListOrder, 
		FirstPatient.Created
FROM		PatientProfile pp
LEFT JOIN	(
				SELECT	ROW_NUMBER() OVER(PARTITION BY MedListsId ORDER BY UPPER(m.Description)) AS Row
				--,		M.*				---- this may not be needed in SELECT here
				,		m.Description
				,		pc.First
				,		pc.Middle
				,		pc.Last
				,		pc.Address1
				,		pc.Address2
				,		pc.City
				,		pc.State
				,		pc.Zip
				,		pr.ListOrder
				,		pr.Created
				,		pr.PatientProfileId 
				FROM	MedLists m
				JOIN	PatientRelationship pr 
				ON		pr.RelationshipTypeMId = m.MedListsId
				JOIN	PatientContacts pc 
				ON		pr.RelatedPartyId = pc.PatientContactsId
				WHERE 	m.Description IN ('GUARDIAN','LAS')
				AND		pr.Type = 5
			) FirstPatient				
ON			pp.PatientProfileId = FirstPatient.PatientProfileId 
AND			FirstPatient.Row = 1
ORDER BY 2, 12

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
awking00

Can you provide some sample data and the expected output so we might have something with which to test?
SOLUTION
Bhavesh Shah

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Dale Burrell

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeff S

ASKER
Thank you all!!