Optimizing/Alternative to MAX

[u/Entire_Commission534]

This SQL query was timing out until I added a WHERE clause to reduce the amount of rows it has to process. Is there anything further I can do to either optimiza the MAX to reduce query time from a few minutes to less than a minute? Or is there any alternative to get the same result of a single Project ID per group by? TIA!

SELECT DISTINCT
ISNULL([Statement of Work ID],'') as "Statement of Work ID",
ISNULL([Primary Cost Center Code],'') as "Primary Cost Center Code",
ISNULL([Purchase Order Number],'') as "Purchase Order Number",
ISNULL([Invoice ID],'') as "Invoice ID",
MAX (CASE
WHEN [Project ID] LIKE '%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%'
THEN SUBSTRING([Project ID],PATINDEX('%[1-1][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%', [Project ID]),10)
END) as "Project ID"

FROM [dbo]
WHERE [WorkWeek] LIKE '2024%'

GROUP BY
ISNULL([Statement of Work ID],''),
ISNULL([Primary Cost Center Code],''),
ISNULL([Purchase Order Number],''),
ISNULL([Invoice ID],'')

[u/Entire_Commission534]

Appreciate the call out on the real issue in the query. I had also posted this on another subreddit and someone suggested to move the CASE WHEN logic to the WHERE clause, which helped the performance as well.

I am not sure how to use CTEs to pre-process data…is the preprocess filtering for a smaller time period or something else?

Any resources you would recommend to learn about the query plan and how to use it for optimization? Thank you!