0

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?

Thanks

2
  • 3
    Please show sample data and desired results, and include rows that have different values for a column you are trying to add to the output. Explain which value you want to show from that column, and why. Think about a baseball player's stats... say you have a query that displays their name and how many home runs they hit (simplified: SELECT name, COUNT(*) FROM dbo.HomeRuns GROUP BY name). If you want to add the distance the home run ball traveled, but you still only want one row per player, which distance do you list? The longest? The shortest? The average? Any? – Aaron Bertrand Jun 22 '20 at 21:10
0

I'm guessing when you're adding the other columns, you are changing the GROUP BY as the columns in the SELECT must be included. That's going to change your results.

Instead, I do the joining and grouping necessary to get the results I wanted, in your case the average, in a sub query and join to that.

Try this:

SELECT
    Max_AvgPrice,
    te.a__IDPK_TreatmentEvent,
    te.Code_Insurance_1
FROM
    (
        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)
    ) AS subquery
    INNER JOIN dbo.TreatmentEvent AS te ON subquery.a_ID_Treatment = te.a_ID_Treatment

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.