Link to home
Start Free TrialLog in
Avatar of Jeff S
Jeff SFlag 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

Avatar of Dale Burrell
Dale Burrell
Flag of New Zealand image

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

Avatar of Bhavesh Shah
they have only two types of relationship?

Guardian or LAS?
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

Avatar of Jeff S

ASKER

There are more then just those two relationships. They are just interested in those two.
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

Avatar of 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

what will be RelationshipTypeMId when there is no data in medlists
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.
Avatar of 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

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

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jeff S

ASKER

Thank you all!!