Jeff S
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.
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
they have only two types of relationship?
Guardian or LAS?
Guardian or LAS?
if so then
this should also work.
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
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
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.
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
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
Here put the RelationshipTypeMId value in place of "-1".
RelationshipTypeMId = where there is no data in medlist.
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
Can you provide some sample data and the expected output so we might have something with which to test?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all!!
Open in new window