You cannot index all those nvarchar fields.  So the sql engine has to do a full table scan to look for the matches.

Based on your terrible table designs, I cant think of anything that will help you.

 

Things that immediately stand out as what will hurt

• The field sizes are way too big...

If you convert your @DB_Users table variable to a temp table, the following code will dynamically create the cross-tab in SQL

DECLARE @Query nvarchar(MAX);

SET @Query = 'WITH cteData...

You can take a look at the following articles by Jeff Moden on dynamically creating PIVOTs and CROSS_TABs

ttps://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

The following query will satisfy your requirements

WITH cteData AS (
  SELECT  t.*
        , rn = ROW_NUMBER() OVER (...

This should give you the same result as your complicated calculation

SELECT @naam = 'I' + CONVERT(char(4), GETDATE(), 12) + '-' + RIGHT('00000' + @MAX_naam, 5)

Can you please show us a partial query so that we can see what you mean.  No need to add all 60 date fields.

If you have something like this

            
April 4, 2022 at 12:31 pm
            
#4010466

            
            
                                                
        

You can use TRY_CAST or TRY_CONVERT

UPDATE #Data
SET Number = TRY_CAST(Number AS float);

Locate the ssms.pkgundef file and edit it as follows

Comment out the following line at the end of the file, and save the file.

• [$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]

Then restart SSMS, and select the theme

...

CursorID is an integer, not a collection.

You need to declare a variable for each field that the cursor returns

 

SQL does not necessarily evaluate a CTE or sub-query when you think it does.  It will un-nest them if it believes that it is more efficient to do so.

Watch the...

...

5. 6070. As I wrote in the initial post, I would expect the inner query to return these rows, a fairly small number, then give me the top one...

The following code satisfies your requirement

SELECT  t.Testid
      , t.Status
      , t.Startdate
     ...

So, taking a guess at what you are looking for ....

WITH cteData AS (
  SELECT t.Testid
       , t.Status
  ...

I have repeated and loaded  the insert statement

insert into table3 values ('1','Progress', '20210203'), ('1', 'Closed', '20220403'), ('2', 'Closed', '20220703'), ('2', 'Progress', '20220503')

it is showing wrong output

March 15, 2022 at 3:24 pm

#3999920