Well this is definitely a head scratcher.  The following code is not pretty, but it seems to get the expected results

WITH cteBase AS (
  SELECT ...

Since you are the one that will be supporting the code and answering if/when it breaks, you really need to try and understand what the code is doing.

The following 2...

Assuming that the data type of [STARTDATE] is date or datetime, and that your "WEEK" is defined as Monday to Sunday, the following SQL should work

SELECT ...

I would look at trying to find a way to reduce the number of rows returned from Parts.Nop_PartsFamilyAttribute [Estimated Number of Rows = 4305960]. Maybe look at changing the INER...

OK, I tried

WITH (FIRSTROW=2, LASTROW=1000, FIELDTERMINATOR=',', ROWTERMINATOR='\r', FIELDQUOTE='quote_characters');

But got the following error

Invalid quote character specified for bulk load. Quote character can be one single byte or Unicode character.

If you...

I have never worked with BULK INSERT, but maybe try adding

FIELDQUOTE = 'quote_characters'

A shot in the dark here .....

A shot in the dark here ..

Since you...

The following code will achieve your requirement.

However, you may want to add code to prevent duplicates

 INSERT INTO #t( id, name )
 SELECT ISNULL((SELECT MAX(id) FROM #t),...

Updating Phil's code, this will cater for the space in [Case ID]

CREATE TABLE #SomeXML ( TheData text NOT NULL );

INSERT  #SomeXML ( TheData )
VALUES ( '<SB><Case...

In addition to the responses above, the following part of the WHERE clause will always result in a table/index scan

AND (@Session = 0 OR @session = SessionID)

That...

i have rephrased the question

DECLARE @s-2 VARCHAR(MAX)= 'r1,r2' SELECT PATINDEX('%[^A-Za-z0-9-())![]]%',@s)

DECLARE @S1 VARCHAR(MAX)= 'X1;r2' SELECT PATINDEX('%[^A-Za-z0-9-())![]]%',@s1)

i need to get first text string  before first comma or semicolan ex "r1" and "x1" respectivily...

DECLARE @ColumnList nvarchar(4000);

SET @ColumnList = STUFF( (SELECT N',' + ac.name
                ...

Using LIKE %SomeVal% is always going to be slow as SQL has to do a table/index scan.

However, I have gotten pretty good performance using Trigram searches

https://sqlperformance.com/2017/09/sql-performance/sql-server-trigram-wildcard-search

You would probably get the best response directly from Brent Ozar

https://www.brentozar.com/contact/