Maybe?:

Select count(*) as cnt, T1.FiYr, T1.DocDte, T1.VenNum,  
Sum(T1.DocCurrAmt) as DocCurrAmt, Max(T2.DocMonthlySum) AS MonthlySum
From dbo.table_name T1
cross apply (
    Select sum(T2.DocCurrAmt) as DocMonthlySum
  ...

• This reply was modified 1 day, 22 hours ago by  ScottPletcher. Reason: Edit: Edited as requested
• This reply was modified 5 hours, 20 minutes ago by  ScottPletcher.

If the queries tend to be by TENANTID -- and I would expect that they are -- then I would definitely uniquely cluster by ( TENANTID, ID ).  If lookups...

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'