If your version of SQL has use of FIRST_VALUE, you could try this:

SELECT DISTINCT team_code, /* , ...*/
    FIRST_VALUE(description) OVER(PARTITION BY team_code ORDER BY...

Is there any way you could provide some sample data?  Say full sets for a couple of designs?

I haven't reviewed it all fully, but I would think you'd want an OUTER APPLY rather than a LEFT JOIN:

...
OUTER APPLY 

(SELECT top (1) pc.PatID, pc.Department, pc.DeptDate

FROM...

How many different dbs do you have?  Do you get any "insufficient threads" messages?  AO uses threads quite a bit.

If you really want to remove the date from the column, then change the column type to "time".

Dates, datetimes and times are stored in a special binary format in SQL Server.  You don't change the column format, you just change the display format.

SELECT CONVERT(varchar(8), datetime_column, 8)

Yes, Oracle is extremely difficult to learn.  Their syntax is overall an odd mix of SQL style and earlier language styles.

No directly usable sample data, so I can't test it, but maybe this:

DECLARE @first_week_ending_date date;
SET @first_week_ending_date = '20240707';

SELECT 
    ClientID,
    DATEADD(DAY,...

Hmm, are you using SSMS / gui to see the tables?  Maybe there is a filter set for schema=dbo?

Good point.  I was in too much of a hurry when I wrote the other code:

DECLARE @x varchar(500) = 'xxx.  #1234 has been replaced by #014521...

An alternative (maybe slightly less overhead?, esp. for long strings):

SELECT LEFT(item, CHARINDEX(' ', item + ' ')) AS value
FROM dbo.DelimitedSplit8K (@x, '#') /*or STRING_SPLIT(), if available to...

I suggest removing ALL non-numeric values in the query itself rather than trying to go thru the data.  For example, by making this mod to the query:

...
...

No DDL provided, but my best guess is that one of these columns:

t1.invoicenum; t2.invoicenum

is integer and the other is not, and that one contains a value of 'VZ34-031'

Since they are "or" conditions, SQL should be able to stop evaluating at the "first" one that is true.  In theory, though, SQL could re-arrange the checks and make a...

I think this will match what you need, if I understand correctly:

SELECT ...

FROM dbo.baseTable bt

LEFT OUTER JOIN dbo.sentTable st ON st.sent_rowid = bt.rowid

WHERE ((@vendor IS NULL AND st.sent_rowid IS NULL)...