Got it to work with some CTE acrobatics:

WITH First_CTE AS (
SELECT DISTINCT
       clm.pat_id,        ...

I have another pesky string_agg for which I'm trying to follow the example above but not getting the results like a field that has the aggregate like: "All, MH":

            
September 6, 2024 at 3:02 pm
            
#4454672

            
            
                                                
        

Thanks Scott! That worked

Thx! I prefer, for now, to look for a subquery since my *real* SQL already has one CTE (I've used 20+ before). I should not have oversimplified my code, but...

It's varchar(15). Casting as varchar(max) was the seemingly only way to avoid this error:

"STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation."

That works until I try to add additional fields to be returned:

select distinct
       i.ins_num
from dbo.tb_pat_ins i
where i.active = 1
  ...

I thought it best to add onto an existing thread since the issue is very similar with a new challenge. I need to return all the insurance numbers that have...

I found this to work perfectly:

  where ServiceDate between DATEADD (mm, DATEDIFF (mm, 0, GETDATE ()) - 1, 0) and 
  dateadd (dd, -1, DATEADD...

I tried this code and it's not perfect but works, but I'm sure there's a better way:

declare @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = dateadd(mm, -1, getdate())
SET @StartDate...

I need for the person's name to be unique. So one of the records for 'Reece, Genais' needs to be eliminated. It doesn't matter which one:

September 9, 2022 at 8:00 pm

#4086013

Solved: Removing the 'DISTINCT' from the very first line made the query run much faster.

I have the query bringing back good data but for just 5,204 records it is taking over 10 minutes. The SQL I shared for solving the problem with the MAX...

Thanks!! I got it to work using cross apply.

I need all the patient ids returned but no duplicates, only the record that has the highest ProcedureCodeId.

Should I be placing my Cross Apply within the CTE in place of MAX or place it in the selection at the bottom?