Help needed with SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed with SQL Query

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2012-10-01 : 09:37:31

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 (even ones not with "Guardian" or "LAS" listed) ... 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 "No Guardian or LAS Listed in Contacts". I need one row per patient - basically, every patient will have either "Guardian", "LAS" or "No Guardian or LAS Listed in Contacts".


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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-01 : 10:31:04

I don't completely understand the relationships between the tables, but it seems to me like you need to partition the row_number column by something - may be this?

...
       LEFT JOIN (
                SELECT ROW_NUMBER() OVER(PARTITION BY m.MedListsId ORDER BY DESCRIPTION) AS Row,
                       M.*
                FROM   MedLists m
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-01 : 10:34:29

[code]
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(PARTITION BY MedListsId ORDER BY CASE Description WHEN 'GUARDIAN' THEN 1 ELSE 2 END) 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
[/code]

Also I dont like the idea of giving ordinal numbers in ORDER BY as at a later time you add or remove a column from SELECT list the result will be ordered differently. I always prefer giving column names themselves

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources