Subquery, MAX, TOP or other ?

[jeffsturgeon2002]

Ive tried many different ways to get this to work right and cant seem to get it. Should I use a Subquery, MAX, TOP or something else?!

I want the last note only for this field"pc.Description AS Notes").

Any help is greatly appreciated!!!

SET NOCOUNT ON

SELECT 
	CONVERT(VARCHAR,pv.visit,101) AS DOS,
 	pv.TicketNumber, 
 	ISNULL(pp.first,'')+ ' '+ ISNULL(pp.last,'')AS [Patient Name], 
 	ISNULL(pp.Address1,'')+ ' '+ ISNULL(pp.Address2,'')+ ' '+ ISNULL(pp.City,'')+ ' '+ ISNULL(pp.State,'')+ ' '+ ISNULL(pp.Zip,'') AS [Patient Address],
 	IsNull(CONVERT(varchar,pp.Birthdate,101), '') AS Birthdate, 
 	pp.PatientId, 
 	ISNULL(ic.ListName,'') AS [Current Carrier], 
 	ISNULL(pi.InsuredId,'') AS [Insured ID], 
 	df.ListName AS Doctor,
 	df1.ListName AS Facility,
	df2.ListName AS Company, 
 	pva.PatBalance AS [Visit Pat Balance], 
 	pva.InsBalance AS [Visit Ins Balance], 
 	ppa.PatBalance AS [Total PatBalance],
 	ppa.InsBalance AS [Total InsBalance], 
 	ISNULL(CONVERT(VARCHAR,g.LastStatement,101),'')AS [Last Statement]
-- 	pc.Description AS Notes,
-- 	MAX(pc.Created)
		
		
FROM  	PatientVisit pv 
	LEFT JOIN DoctorFacility df ON pv.DoctorId = df.DoctorFacilityId 
	LEFT OUTER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId 
	LEFT JOIN PatientInsurance pi ON pv.CurrentPICarrierId = pi.PatientInsuranceId 
	LEFT JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId AND pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId 
	LEFT JOIN PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId 
	LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileId = ppa.PatientProfileId 
	INNER JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId  
	LEFT OUTER JOIN DoctorFacility df1 ON pv.FacilityId = df1.DoctorFacilityId 
	LEFT JOIN [DoctorFacility] AS df2 ON pv.[CompanyId]= df2.[DoctorFacilityId]
	LEFT OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
 	LEFT JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId AND pv.PatientVisitId = pc.PatientVisitId 
	
WHERE pv.PatientVisitId = ('6')

-- GROUP BY pv.visit, pv.ticketnumber, pp.first, pp.last, pp.address1, pp.address2, pp.city, pp.state, pp.zip,
-- 	 pp.birthdate, pp.patientid, ic.listname, PI.insuredid, df.listname, df1.listname, df2.listname, pva.patbalance,
-- 	 pva.insbalance, ppa.patbalance, ppa.insbalance, g.laststatement, pc.description,pc.created

Jeff

SELECT * FROM users WHERE clue > 0

 

[jeffsturgeon2002]

Took me a bit, but came up with this code....

(SELECT TOP 1 description FROM PatientCorrespondence WHERE PatientProfileID = pp.PatientProfileID AND PatientVisitID = pv.PatientVisitID ORDER BY Created DESC) AS Notes

Jeff

SELECT * FROM users WHERE clue > 0