I want the last note only for this field:("pc.Description AS Notes").
Any help is greatly appreciated!!!
CODE
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
As you suggested there are a few ways you could do it, a subquery should be as good as any, and simpler than most others
SELECT ..., (SELECT TOP 1 description
FROM PatientCorrespondence
WHERE PatientProfileID = pp.PatientProfileID AND PatientVisitID = pv.PatientVisitID
ORDER BY Created DESC) AS Notes
FROM ...
