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

Last Comment

Jeff S

8/22/2022 - Mon

Dale Burrell

9/30/2012

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

9/30/2012

they have only two types of relationship?

Guardian or LAS?

Bhavesh Shah

9/30/2012

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

10/1/2012

ASKER

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

Bhavesh Shah

10/1/2012

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

10/1/2012

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

10/1/2012

what will be RelationshipTypeMId when there is no data in medlists

Bhavesh Shah

10/1/2012

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

10/1/2012

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