sql server - Having trouble with a max date in SQL

Question

So, new to writing SQL querys and reports, and I have a doozy.. for me anyway. Trying to only grab the latest auth_date including ties for that date. Because for each date there are multiple entries. This report works, but it returns all auth dates and not just the latest. For example, I have 6 entries for 11/9/2020 and 6 for 12/11/2020 and would only like the ones from 12/11/2020. Here is my code. Any help would be appreicated. TSQL

Select Clients.first_name,
  Clients.last_name,
  Clients.client_id,
  Authorizations.payer_id,
  Max(Authorizations.auth_date) As Max_auth_date,
  Authorizations.period_start,
  Authorizations.period_end,
  Clients.client_status,
  Authorizations.pending,
  Authorizations.auth_units,
  Authorizations.used_units,
  Authorizations.ext_units_used,
  Authorizations.auth_number,
  Authorizations.period_type,
  Authorizations.count_units,
  Authorizations.requested_units,
  Payer.payer_description,
  Team.team_name
From Authorizations
  Left Join Clients On Authorizations.client_id = Clients.client_id
  Inner Join Payer On Payer.payer_id = Authorizations.payer_id And
    Authorizations.payer_id = Payer.payer_id And Authorizations.payer_id =
    Payer.payer_id
  Right Join TeamClient On Clients.client_id = TeamClient.client_id
  Inner Join Team On Team.team_id = TeamClient.team_id And TeamClient.team_id =
    Team.team_id
  Inner Join EmployeeClients On Authorizations.client_id =
    EmployeeClients.client_id
Where EmployeeClients.primary_flag = 'True' And EmployeeClients.emp_id = @param1
Group By Clients.first_name,
  Clients.last_name,
  Clients.client_id,
  Authorizations.payer_id,
  Authorizations.period_start,
  Authorizations.period_end,
  Clients.client_status,
  Authorizations.pending,
  Authorizations.auth_units,
  Authorizations.used_units,
  Authorizations.ext_units_used,
  Authorizations.auth_number,
  Authorizations.period_type,
  Authorizations.count_units,
  Authorizations.requested_units,
  Payer.payer_description,
  Team.team_name
Order By Clients.last_name,
  Clients.first_name

question from:https://stackoverflow.com/questions/66055224/having-trouble-with-a-max-date-in-sql

Answer 1

Waitting for answers