MAX needed here?

[jeffsturgeon2002]

I want the last record only for the field pc.[Description]. Should I use the MAX and if so, how do I do it?

SELECT DISTINCT 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, 
		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]

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 OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
	LEFT JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId AND pv.PatientVisitId = pc.PatientVisitId

ORDER BY pv.[TicketNumber] ASC

Jeff

SELECT * FROM users WHERE clue > 0

 

[AlexCuse]

You are missing a comma after [Last Statement]. Is that typo?

If so, you should be able to change the part of your seelect query to

max(pc.[Description])

You will also need to add all other fields to your Group By.

Hope this helps, and Happy Holidays. Sorry to see you are working today!

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jeffsturgeon2002]

Thanks Alex. I am just getting a leg up on tomorrows workload thats all. The kiddos are asleep finally after a busy day, so I thought I'd sneak off and get a head start.

I took your suggestion and got my results back however it isnt giving me the last note entered still. Shouldn't the MAX syntax return the last or most recent note entered? Why did I have to enter the values into a Group By when I used Max? (Still learning the ropes, so I thought Id ask this one.)

SELECT DISTINCT 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, 
		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],
		MAX(pc.Description)AS Notes
		
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 OUTER JOIN PatientVisitProcs pvp ON pv.PatientVisitId = pvp.PatientVisitId
	LEFT JOIN PatientCorrespondence pc ON pp.PatientProfileId = pc.PatientProfileId AND pv.PatientVisitId = pc.PatientVisitId

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, pva.patbalance, pva.insbalance, 
ppa.patbalance, ppa.insbalance, g.laststatement

ORDER BY pv.[TicketNumber] ASC

Jeff

SELECT * FROM users WHERE clue > 0

 

[AlexCuse]

Well that's good. I forgot to mention that for this to return the latest description, description needs to be something that goes up as new records are added. Preferably a date modified or something like that.

The reason that you need to add the group bys is because of the way aggregate functions work. You are basically taking the maximum value of description from all rows where all other fields are equal. This is what your group by accomplishes. Consider this example:

Sample Data:

ColA      ColB          ColC
1           2             1
1           2             3
3           1             2
 
Sample Query:

select ColA, ColB, max(ColC) as [MCC] from SampleData
group by ColA, ColB

Results from this query will be as follows:

ColA        ColB         MCC
1            2            3
3            1            2

So all rows with ColA and ColB equal, become one row in the result (thanks to Group By), and resulting row shows maximum value for ColC.

So if you are not getting this type of result (and description is a field where maximum value = latest record), I would say that some other field is probably not containing the same value. Whatever field this is, you will need to use an aggregate on (sum, min, max, etc...) in order to condense your result to one row(and remove this field from your group by).

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[jeffsturgeon2002]

Alex,

Could/Should I add in the "Created" field and use this for the MAX instead of the actual note field? This is a datetime field.

Would I change my "MAX(pc.Description)AS Notes" back to pc.Description as Notes and add the Max to the Created - "MAX(pc.Created)AS Created"?

Jeff

SELECT * FROM users WHERE clue > 0

 

[AlexCuse]

I don't think this would be that good of a scenario to use a computed column, but I have not seen your data or desired result. Were you able to get the results you wanted using MAX?

Alex

Ignorance of certain subjects is a great part of wisdom