SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help needed with SQL Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JeffS23
Posting Yak Master

210 Posts

Posted - 10/01/2012 :  09:37:31  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/01/2012 :  10:31:04  Show Profile  Reply with Quote
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
...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/01/2012 :  10:34:29  Show Profile  Reply with Quote

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


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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000