My bad... debugging clearly showed I had failed to declare the varchar variable '@dateChoice' with a length.... Thanks! It works now:

DECLARE@startDate datetime = '11/1/2024'
   ,@endDate...

This does not bring back any records unfortunately:

SELECT DISTINCT tp.client_id AS Member_ID
, tp.PaidDate AS Paid_Date
, tp.DOS
FROM #ins_temp tp
WHERE (@dateChoice = 'Paid_Date' and tp.PaidDate BETWEEN @startDate AND @endDate)
or...

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.