The below code is working for me to pull out the max Avg Price, but I need to add another couple of fields from the dbo.TreatmentEvent table, when I add another column it stops giving me the max and instead just starts listing out all values.
SELECT Max_AvgPrice = MAX(pav.AvgPrice), te.a_ID_Treatment FROM dbo.TreatmentEvent te LEFT JOIN dbo.dim_Procedure_Codes pco ON te.Code_Insurance_1 = pco.a_IDPK_ProcedureCode LEFT JOIN dbo.dim_Procedure_Category pca ON pco.a_ID_ProcedureCategory = pca.a_IDPK_ProcedureCategory LEFT JOIN dbo.dim_Procedure_AvgPrice pav ON pco.a_ID_ProcedureGrouping = pav.a_ID_ProcedureGrouping GROUP BY te.a_ID_Treatment, YEAR(te.TreatmentEventStartDate), pca.a_IDPK_ProcedureCategory, te.a_ID_TreatmentEventStatus HAVING (YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) OR YEAR(te.TreatmentEventStartDate) = YEAR(GETDATE()) - 1) AND (pca.a_IDPK_ProcedureCategory = 1) AND (te.a_ID_TreatmentEventStatus = 3);
When I add in other columns this is an example the result;
a_ID_Treatment a__IDPK_TreatmentEvent Code_Insurance_1 Max_AvgPrice 454646 432023 455 1030 454646 431168 194 719
What I need to get is just the first line with the AvgPrice of 1030, but I need to see the other columns too that apply to that line.
Anyone have any suggestions?