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

SELECT PatientID, MAX(ProcedureCodeID) AS MaxCodeID

FROM t

GROUP BY PatientID

If you need other columns too,...

Use CROSS APPLY to return the first record

SELECT p.PatientID...

ca.ProcedureCodeID

FROM Patient p

CROSS APPLY (SELECT TOP 1 ProcedureCodeID

FROM SomeTable t

WHERE t.PatientID = p.PatientID

ORDER BY <some column>) ca

Okay, since you don't want to provide data, we can't help you.

Case closed.

Good luck!

What's the [EndDate] that matches with [Created Date] so we know how long a duration is?

If you want useful help, please read and follow the instructions in this article:

Can't you just use OR in your WHERE clause and then do the aggregation? I'm not convinced you need a CTE at all.

Maybe start here - Cathrine Wilhelmsen's tutorial

#GotData?

Without a table with some data, it's hard to help.

This is a whole lot easier with some [fake] data.

Does a case have a Close Date or similar? If you have that and a Calendar table, then this is absolutely...

• This reply was modified 3 years, 4 months ago by pietlinden.

Okay, I totally stole this from an article that Kenneth Fisher wrote (@sqlstudies144). Basically, you use CROSS APPLY to "stack" the repeating groups, and then you query that. Here's my...

=iif((CountRows("SrvyRespondents") < 1 ), True, False).

is an odd way to express that. CountRows will return a positive number or zero. So why not

=iif(CountRows("SrvyRespondents") = 0, True, False)

You could do something...

• This reply was modified 3 years, 4 months ago by pietlinden.

Oh, NULLIF!! I knew I was missing something with the NULLs. <g>

Transpose table, do a distinct count, transpose back.

DECLARE @EndTargetDate DATE = '01-Jan-2022'

SELECT

FROM

WHERE SomeDate > DATEADD(week,-4,@EndTargetDate) AND SomeDate <= @EndTargetDate

like this?

SELECT  
FieldCount = (CASE WHEN Prg1 ='' THEN 0 ELSE 1 END + 
CASE WHEN Prg2 ='' THEN 0 ELSE 1 END +
CASE WHEN Prg3...

I wouldn't choose to create a table with that structure, but this should work:

SELECT VID, OrdDate, COUNT(DISTINCT(v.val))
FROM #T1
CROSS APPLY (VALUES (Prg1),(Prg2),(Prg3),(Prg4),(Prg5),(Prg6)) v(val)
GROUP BY VID, OrdDate;