You could do as below.  You can't really GROUP BY ID, because that would separate the Small, Medium and Large into separate rows.

SELECT    ...

How is one to know when it's, say, 8AM or 9AM, and the last entry for a person was at 2AM, whether future entries were missed or the person is...

IF you're on Enterprise Edition, you can do an online build of an index for SpoolStartDt.  Then you can use that index to do the DELETEs.  If you're not on...

I haven't ultra-tuned this, but see if it gives you the correct results.

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally_hrs AS (
 ...

Maybe?:

SELECT SYMBOL1, SYMBOL2
FROM (
    SELECT SYMBOL1, ROW_NUMBER() OVER(ORDER BY SYMBOL1) AS row_num_1
    FROM #STOCKS1 S1
    WHERE NOT EXISTS(SELECT...

Since it's not an index seek, partitioning should still help the query by reducing the number of pages it has to search.  Once again, verify that by looking at the...

It's just a predicate / search, it's not an index SEEK.

Interesting.  Look at the query plan and see if it is doing a SEEK as part of the clus index scan.  I'll read up on the mechanics of row-level security...

I'll address your qs/ concerns as (A) ["Does partitioning ..."], (B) and (C):

(A) Yes, absolutely.  You only need to partition then for other aspects, such as manageability.

(B) In the vast...

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 years, 1 months ago by ScottPletcher. Reason: Edit: Edited as requested
• This reply was modified 1 years, 1 months 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.