T-SQL optimization needed

Question

T-SQL optimization needed

Naomi Nosonovsky 8,906

Hi everybody,

I have the following code which I have commented:

      --CASE
           --    WHEN Status = 'Termed' THEN
           --        CASE
           --            WHEN ProviderType IN ( 'Group, Solo' ) THEN
           --                NULL
           --            WHEN ProviderType = 'Rendering' THEN
           --            (
           --                SELECT TOP (1)
           --                       dt
           --                FROM
           --                (
           --                    SELECT LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi, tin_number ORDER BY [cte].[OHCS Term Date]) AS dt
           --                    FROM cte a
           --                    WHERE a.npi = cte.npi
           --                          AND a.tin_number = cte.tin_number
           --                    UNION ALL
           --                    SELECT MAX(hn.load_date)
           --                    FROM rv.hub_npi hn
           --                    WHERE NOT EXISTS
           --                    (
           --                        SELECT 1
           --                        FROM stg.stg_facets_full f
           --                        WHERE f.npi_hk = hn.npi_hk
           --                              AND f.primary_contracted_relationship = 'Clinician Only'
           --                    )
           --                          AND hn.source = 'FACETS'
           --                ) subselect
           --                ORDER BY dt DESC
           --            )
           --        END
           --END AS [SG Latest Term Date]

With this code uncommented I had to kill the query after 8 minutes. With this code commented the query executed in 7 seconds. Do you have suggestions as how I may re-write it to improve performance.

The business rules said: "If record Status = Termed: Group or Solo: Copy ""OHCS Term Date"" and store here. Rendering: Store latest term date from OHCS daily file or date NPI disappeared from OHBS daily file. Join on NP. "

Viorel 126.9K Reputation points

2025-08-19T14:49:25.56+00:00

Perhaps the expression should first be revised, in the sense that IN ( 'Group, Solo' ) needs to be replaced with IN ( 'Group', 'Solo' ), and a.npi = cte.npi AND a.tin_number = cte.tin_number, which seems to always be TRUE, should be corrected. If such remarks have sense, the optimisation can continue after corrections.

Naomi Nosonovsky 8,906

Re-wrote that as following but still bad performance:

 --WHEN Provider_Type = 'Rendering' THEN

                            --  (SELECT TOP (1) dt FROM

                            --    (VALUES ((SELECT TOP (1) LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi ORDER BY [a].[OHCS Term Date]) AS dt

                            --        FROM #FirstResult a

                            --        WHERE a.npi = cte.npi)), 

                                    

                                    

                            --        ((SELECT MAX(hn.load_date) AS dt

                            --        FROM rv.hub_npi hn

                            --        WHERE NOT EXISTS

                            --        (

                            --            SELECT 1 FROM stg.stg_facets_full f WHERE f.npi_hk = hn.npi_hk

                            --        )

                            --              AND hn.source = 'FACETS'

                            --   ))) derived(dt) ORDER BY dt DESC)

Also, strange error appears:

Msg 208, Level 16, State 1, Procedure im_automation.accent_changes_New, Line 502 [Batch Start Line 2]

Invalid object name 'cte'.

I don't see why is that, the code compiles fine and it's on this line

SELECT 'Automation OHCS' AS source,
           cte.Request_Type,
          ** cte.Change_Type,**
           cte.[Exclusion_Reason],
           -- 'Professional' AS category,
           CAST(cte.Provider_Type AS VARCHAR(10)) AS provider_type,
           CAST(cte.Social_Group AS VARCHAR(3)) AS SocialGroup,
           CAST(cte.Status AS VARCHAR(10)) AS status,
           CAST(cte.[OHCS Eff Date] AS DATE) AS effective_date,
           CAST(CASE
                    WHEN cte.Provider_Type IN ( 'Group, Solo' ) THEN
                        [cte].[OHCS Eff Date]
                    WHEN cte.Provider_Type = 'Rendering' THEN

Why there is an error on a perfectly valid code and how should I fix the Rendering case to work? For now I commented the code again.

Answer accepted by question author

0 additional answers

Your answer

Viorel 126.9K Reputation points

2025-08-19T14:49:25.56+00:00

Perhaps the expression should first be revised, in the sense that IN ( 'Group, Solo' ) needs to be replaced with IN ( 'Group', 'Solo' ), and a.npi = cte.npi AND a.tin_number = cte.tin_number, which seems to always be TRUE, should be corrected. If such remarks have sense, the optimisation can continue after corrections.
Naomi Nosonovsky 8,906 Reputation points

2025-08-19T16:13:13.7133333+00:00

Re-wrote that as following but still bad performance:

--WHEN Provider_Type = 'Rendering' THEN -- (SELECT TOP (1) dt FROM -- (VALUES ((SELECT TOP (1) LAST_VALUE([OHCS Term Date]) OVER (PARTITION BY npi ORDER BY [a].[OHCS Term Date]) AS dt -- FROM #FirstResult a -- WHERE a.npi = cte.npi)), -- ((SELECT MAX(hn.load_date) AS dt -- FROM rv.hub_npi hn -- WHERE NOT EXISTS -- ( -- SELECT 1 FROM stg.stg_facets_full f WHERE f.npi_hk = hn.npi_hk -- ) -- AND hn.source = 'FACETS' -- ))) derived(dt) ORDER BY dt DESC)

Also, strange error appears:

Msg 208, Level 16, State 1, Procedure im_automation.accent_changes_New, Line 502 [Batch Start Line 2]

Invalid object name 'cte'.

I don't see why is that, the code compiles fine and it's on this line

SELECT 'Automation OHCS' AS source, cte.Request_Type, ** cte.Change_Type,** cte.[Exclusion_Reason], -- 'Professional' AS category, CAST(cte.Provider_Type AS VARCHAR(10)) AS provider_type, CAST(cte.Social_Group AS VARCHAR(3)) AS SocialGroup, CAST(cte.Status AS VARCHAR(10)) AS status, CAST(cte.[OHCS Eff Date] AS DATE) AS effective_date, CAST(CASE WHEN cte.Provider_Type IN ( 'Group, Solo' ) THEN [cte].[OHCS Eff Date] WHEN cte.Provider_Type = 'Rendering' THEN

Why there is an error on a perfectly valid code and how should I fix the Rendering case to work? For now I commented the code again.

Answer 1

Joerg 62 196

Hi,

change your subselects in the query first in CTE or in temp table, and then join the CTE or temp table in your final query.

Regards Jörg

Naomi Nosonovsky 8,906 Reputation points

2025-08-19T18:53:58.06+00:00

Thanks, @Joerg 62 This did the trick and now results come in 6 sec.
Naomi Nosonovsky 8,906 Reputation points

2025-08-22T18:11:47.9266667+00:00

Hi @Joerg 62 ,

Your query worked at first, but I made some changes and now the whole procedure executes in 9 minutes, which is too long, in my opinion. I can post the whole procedure now and may be you can find some other ways to optimize. I am also going to see if I am able (have permissions) to get the actual query plan.
Naomi Nosonovsky 8,906 Reputation points

2025-08-22T18:19:23.2833333+00:00

Unfortunately, I'm unable to post my procedure code in one code block as apparently it's too long for the forum to handle. I'm getting a red message than I cannot post the comment. Not sure how to put the proc code for you to help me.

Naomi Nosonovsky 8,906

Viorel, I didn't see your comment at first, but I now understood what did you mean by IN ('Group, Solo') to ('Group', 'Solo'). Good catch, I fixed that now. The query still takes long time to execute. Here it is:

WITH cteEarliestEffectiveDate(dt, npi) AS  (SELECT FIRST_VALUE(f.[OHCS Eff Date]) OVER (PARTITION BY npi ORDER BY f.[OHCS Eff Date]) AS dt, npi
                            FROM #FirstResult f
                            UNION ALL
                            SELECT MIN(f.firstcontracteffectivedate) AS dt, npi
                            FROM stg.facets_full f
                            GROUP BY npi
				) 
    SELECT 'Automation OHCS' AS source,
           cte.Request_Type,
           cte.Change_Type,
           cte.[Exclusion_Reason],
           -- 'Professional' AS category,
           CAST(cte.Provider_Type AS VARCHAR(10)) AS provider_type,
           CAST(cte.Social_Group AS VARCHAR(3)) AS SocialGroup,
           CAST(cte.Status AS VARCHAR(10)) AS status,
           CAST(cte.[OHCS Eff Date] AS DATE) AS effective_date,
           CAST(CASE
                    WHEN cte.Provider_Type IN ( 'Group', 'Solo' ) THEN
                        [cte].[OHCS Eff Date]
                    WHEN cte.Provider_Type = 'Rendering' THEN
                       (SELECT TOP (1) dt  FROM cteEarliestEffectiveDate ct
					   WHERE ct.npi = cte.npi ORDER BY dt )
             END AS DATE) AS earliest_effective_date,
           CAST([cte].[OHCS Change Date] AS DATE) AS change_date,
           CAST([OHCS Term Date] AS DATE) AS term_date,
           CAST(NULL AS DATE) AS latest_term_date,
           cte.tin_number,
           cte.npi,
           CAST(cte.provider_id AS VARCHAR(50)) AS provider_id,
           CAST(cte.Prov_Last_Name AS VARCHAR(100)) AS provider_last_name,
           CAST(cte.prov_first_name AS VARCHAR(30)) AS provider_first_name,
           CAST(cte.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
           CAST(cte.clinic_name AS VARCHAR(100)) AS clinic_name,
           -- cte.prov_group,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_specialty
                END AS VARCHAR(100)) AS t50_specialty,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_provider_type
                END AS VARCHAR(100)) AS t50_provider_type,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.taxonomy
                END AS VARCHAR(10)) AS t15_taxonomy,
           CAST(cte.license_number AS VARCHAR(15)) AS license_number,
           CAST(cte.license_state AS VARCHAR(50)) AS license_state,
           CAST(cte.license_issue_date AS DATE) AS license_issue_date,
           CAST(cte.loc_address1 AS VARCHAR(50)) AS svc_address_line1,
           CAST(cte.loc_address2 AS VARCHAR(50)) AS svc_address_line2,
           CAST(cte.loc_city AS VARCHAR(30)) AS svc_city,
           CAST(cte.loc_state AS VARCHAR(2)) AS svc_state,
           CAST(cte.loc_zip AS VARCHAR(9)) AS svc_zip,
           CAST(cte.remit_address1 AS VARCHAR(50)) AS remit_address_line1,
           CAST(cte.remit_address2 AS VARCHAR(50)) AS remit_address_line2,
           CAST(cte.remit_city AS VARCHAR(30)) AS remit_city,
           CAST(cte.remit_state AS VARCHAR(2)) AS remit_state,
           CAST(cte.remit_zip AS VARCHAR(9)) AS remit_zip
    FROM #Additions cte
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte.specialty = tx.specialty
    UNION ALL
    SELECT 'Automation OHCS' AS source,
           cte1.Request_Type,
           cte1.Change_Type,
           cte1.Exclusion_Reason,
           CAST(cte1.Provider_Type AS VARCHAR(10)) AS provider_type,
           CAST(cte1.Social_Group AS VARCHAR(3)) AS SocialGroup,
           CAST(cte1.Status AS VARCHAR(10)) AS status,
           CAST(cte1.[OHCS Eff Date] AS DATE) AS effective_date,
           CAST(CASE
                    WHEN cte1.Provider_Type IN ( 'Group', 'Solo' ) THEN
                        [cte1].[OHCS Eff Date]
                    WHEN cte1.Provider_Type = 'Rendering' THEN
                       (SELECT TOP (1) dt  FROM cteEarliestEffectiveDate ct
					   WHERE ct.npi = cte1.npi ORDER BY dt )
             END AS DATE) AS earliest_effective_date,
           CAST([cte1].[OHCS Change Date] AS DATE) AS change_date,
           CAST(cte1.[OHCS Term Date] AS DATE) AS term_date,
           CAST(NULL AS DATE) AS latest_term_date,
           cte1.tin_number,
           cte1.npi,
           CAST(cte1.provider_id AS VARCHAR(50)) AS provider_id,
           CAST(cte1.Prov_Last_Name AS VARCHAR(100)) AS provider_last_name,
           CAST(cte1.prov_first_name AS VARCHAR(30)) AS provider_first_name,
           CAST(cte1.prov_mi AS VARCHAR(30)) AS provider_middle_initial,
           CAST(cte1.clinic_name AS VARCHAR(100)) AS clinic_name,
           -- cte.prov_group,
           CAST(CASE
                    WHEN cte1.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte1.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_specialty
                END AS VARCHAR(100)) AS t50_specialty,
           CAST(CASE
                    WHEN cte1.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte1.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.T50_provider_type
                END AS VARCHAR(100)) AS t50_provider_type,
           CAST(CASE
                    WHEN cte1.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte1.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        tx.taxonomy
                END AS VARCHAR(10)) AS t15_taxonomy,
           CAST(cte1.license_number AS VARCHAR(15)) AS license_number,
           CAST(cte1.license_state AS VARCHAR(50)) AS license_state,
           CAST(cte1.license_issue_date AS DATE) AS license_issue_date,
           CAST(cte1.loc_address1 AS VARCHAR(50)) AS svc_address_line1,
           CAST(cte1.loc_address2 AS VARCHAR(50)) AS svc_address_line2,
           CAST(cte1.loc_city AS VARCHAR(30)) AS svc_city,
           CAST(cte1.loc_state AS VARCHAR(2)) AS svc_state,
           CAST(cte1.loc_zip AS VARCHAR(9)) AS svc_zip,
           --    cte.svc_county,
           CAST(cte1.remit_address1 AS VARCHAR(50)) AS remit_address_line1,
           CAST(cte1.remit_address2 AS VARCHAR(50)) AS remit_address_line2,
           CAST(cte1.remit_city AS VARCHAR(30)) AS remit_city,
           CAST(cte1.remit_state AS VARCHAR(2)) AS remit_state,
           CAST(cte1.remit_zip AS VARCHAR(9)) AS remit_zip
    FROM #Changes cte1
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte1.specialty = tx.specialty
               AND tx.source = 'OHCS'
    UNION ALL

Then this select repeated 1 more time for #Terminations.

Viorel 126.9K Reputation points

2025-08-22T19:33:14.9066667+00:00

Did you identify again the slowest subquery? (The subqueries are separated by UNION ALL).
Naomi Nosonovsky 8,906 Reputation points

2025-08-22T19:43:28.94+00:00

Not yet, the execution plan is also quite complex. Is there a way to get it in human readable form, not graphical?
Joerg 62 196 Reputation points

2025-08-24T20:09:04.48+00:00

You can check STATISTICS TIME { ON | OFF } - each query separated by UNION ALL

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql?view=sql-server-ver17

Regards Jörg
Naomi Nosonovsky 8,906 Reputation points

2025-09-01T14:50:08.99+00:00

This is the result from SET STATISTICS TIME ON/OFF:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 16 ms, elapsed time = 18 ms.

SQL Server Execution Times:

CPU time = 16 ms, elapsed time = 18 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 15 ms, elapsed time = 17 ms.

SQL Server parse and compile time:

CPU time = 9 ms, elapsed time = 9 ms.

SQL Server Execution Times:

CPU time = 193685 ms, elapsed time = 54154 ms.

SQL Server parse and compile time:

CPU time = 62 ms, elapsed time = 70 ms.

SQL Server Execution Times:

CPU time = 114719 ms, elapsed time = 120032 ms.

Completion time: 2025-09-01T09:44:39.6822235-05:00
Naomi Nosonovsky 8,906 Reputation points

2025-09-01T14:58:28.99+00:00

This is the code I'm trying to optimize:

ProcedureCode.txt
Naomi Nosonovsky 8,906 Reputation points

2025-09-01T15:53:45.0566667+00:00

These are results from SET STATISTICS IO ON:

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 8 ms.

Table '#FirstResult________________________________________________________________________________________________________0000000004A2'. Scan count 327, logical reads 2040391, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'hub_npi'. Scan count 0, logical reads 1932, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 3840816, logical reads 60439726, physical reads 0, page server reads 0, read-ahead reads 51198, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:

CPU time = 192967 ms, elapsed time = 55155 ms.

SQL Server parse and compile time:

CPU time = 71 ms, elapsed time = 71 ms.

Table 'Worktable'. Scan count 4306766, logical reads 61455344, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'facets_full'. Scan count 322, logical reads 1299, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table '#FirstResult________________________________________________________________________________________________________0000000004A2'. Scan count 322, logical reads 2034074, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table '#Terminations_______________________________________________________________________________________________________0000000004A5'. Scan count 1, logical reads 159, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'sat_pdm_specialty_taxonomy'. Scan count 6, logical reads 12, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'hub_taxonomy'. Scan count 0, logical reads 12, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'hub_specialty'. Scan count 0, logical reads 12, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'lnk_specialty_taxonomy'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table '#Changes____________________________________________________________________________________________________________0000000004A4'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table '#Additions__________________________________________________________________________________________________________0000000004A3'. Scan count 1, logical reads 1, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

SQL Server Execution Times:

CPU time = 114000 ms, elapsed time = 120068 ms.

Completion time: 2025-09-01T10:14:04.3691500-05:00
Joerg 62 196 Reputation points

2025-09-01T20:03:43.6166667+00:00

May be the problem is the SELECT TOP (n) in your querys.

Have a look on:

https://docs.microsoft.com/en-us/answers/questions/625121/sql-server-query-performance.html

https://www.sqlshack.com/explore-sql-queries-hint-option-fast-n/

and test your query with

OPTION (USE HINT('DISABLE_OPTIMIZER_ROW_GOAL'))

OR

OPTION (FAST 'N')

Regards

Jörg
Naomi Nosonovsky 8,906 Reputation points

2025-09-25T02:12:13.6666667+00:00

im_automation.accent_changes2.txt

I made more modifications in the query as the BR changed a bit, and now the query never finishes. I may need a second set of eyes to suggest more tricks for the performance.

Attached is the new text of the procedure.
Joerg 62 196 Reputation points

2025-09-28T20:28:35+00:00

Hey,

check the ideas for performance your query from ChatGPT:

https://1drv.ms/b/c/7140dd2db21fe5b3/Ecg5gzpKZsRAuJL__d4wtbEBAiUxIQ8NgArhjWkg6t2GgQ?e=wzIID6

Note Point 6. Viorel wrote about this:
Did you identify again the slowest subquery? (The subqueries are separated by UNION ALL).

Regards Jörg

Naomi Nosonovsky 8,906

Why this statement produces an error:

Msg 116, Level 16, State 1, Procedure im_automation.accent_changes, Line 371 [Batch Start Line 2]

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    ;WITH latestTermDate AS (SELECT MAX(status_date) AS dt, tin_number
	                         FROM #FirstResult
							 GROUP BY tin_number
						UNION ALL
    SELECT MAX(hn.load_date) AS dt,
           tin AS tin_number
    FROM rv.hub_tin hn
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM stg.stg_facets_full f
        WHERE f.tin_hk = hn.tin_hk
              AND f.primary_contracted_relationship = 'Clinician'
   
          AND hn.source = 'FACETS')
    GROUP BY hn.tin)

	UPDATE f
	SET [f].[Latest Term Date] = (SELECT MAX(dt), latestTermDate.tin_number FROM latestTermDate 
	                              GROUP BY latestTermDate.tin_number) 

	FROM #FirstResult f 
	JOIN  LatestTermDate cc ON cc.tin_number = f.tin_number
	WHERE Provider_Type IN ('Group', 'Solo') AND Status = 'Termed';

Naomi Nosonovsky 8,906 Reputation points

2025-10-05T19:43:36.7466667+00:00

Also, for some unknown reason I the QUERY hint is not allowed.

Share via

T-SQL optimization needed

0 additional answers

Your answer