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