Unexplainable results

Thanks for your answer Azer,

Attached is the actual statement:

SET STATISTICS IO ON 
	SET STATISTICS PROFILE ON 
    CREATE NONCLUSTERED INDEX idxNpi ON #FirstResult (npi);
    CREATE NONCLUSTERED INDEX idxTIn ON #FirstResult (tin_number);
    CREATE NONCLUSTERED INDEX idxTInNpi ON #FirstResult (tin_number, npi);
   
    SELECT 'Automation OHCS' AS [Source],
           'Professional' AS Category,
           cte.Provider_Type AS ProvType,
           CAST(cte.Social_Group AS VARCHAR(3)) AS [Social Group],
           [Social Group Partner],
           '' AS [Tracer ID],
           COALESCE(Request_Type, '') AS [Request Type],
           [Change_Type] AS [Change Type],
           CAST(cte.[Status] AS VARCHAR(10)) AS [Status],
           CAST(cte.[OHCS Eff Date] AS DATE) AS [OHCS Eff Date],
           CAST(cte.[Earliest Eff Date] AS DATE) AS [Earliest Eff Date],
           cte.[OHCS Change Date],
           CASE
               WHEN cte.[Status] = 'Termed' THEN
                   CAST(cte.[OHCS Term Date] AS DATE)
               ELSE
                   NULL
           END AS [OHCS Term Date],
           CASE
               WHEN cte.[Status] = 'Termed' THEN
                   [cte].[Latest Term Date]
           END AS [Latest Term Date],
           cte.[Social Grouping Term Date],
           cte.Exclusion_Reason AS ExclusionReason,
           cte.tin_number AS TinNumber,
           cte.npi AS NPI,
           CAST(cte.provider_id AS VARCHAR(50)) AS ProviderID,
           CAST(cte.Prov_Last_Name AS VARCHAR(18)) AS ProviderLastName,
           CAST(cte.prov_first_name AS VARCHAR(30)) AS ProviderFirstName,
           CAST(cte.prov_mi AS VARCHAR(30)) AS ProviderMI,
           CAST(cte.clinic_Name AS VARCHAR(39)) AS ClinicName,
		   CAST(cte.Generation   AS VARCHAR(30)) AS Generation,
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '70'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_specialty) OVER ()
                END AS VARCHAR(100)) AS [T50 Specialty],
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '24'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.T50_provider_type) OVER ()
                END AS VARCHAR(100)) AS [T50 ProvType],
           CAST(CASE
                    WHEN cte.Provider_Type = 'Group' THEN
                        '193200000X'
                    WHEN cte.Provider_Type IN ( 'Solo', 'Rendering' ) THEN
                        MIN(tx.taxonomy) OVER ()
                END AS VARCHAR(10)) AS [T15 Taxonomy],
           CAST(CASE
                    WHEN cte.loc_state = cte.license_state
                         AND cte.Provider_Type IN ( 'Rendering', 'Solo' ) THEN
                        CASE WHEN cte.license_number ='' THEN '' ELSE MIN(cte.license_number) OVER () END
                    ELSE
                        ''
                END AS VARCHAR(15)) AS LicenseNumber,
           CASE
               WHEN cte.Provider_Type IN ( 'Rendering', 'Solo' )
                    AND cte.loc_state = cte.license_state
                    AND cte.license_number = MIN(cte.license_number) OVER ()  AND cte.license_number <> '' THEN
                   CAST(cte.license_state AS VARCHAR(50))
               ELSE
                   ''
           END AS LicenseState,
           CASE
               WHEN cte.Provider_Type IN ( 'Rendering', 'Solo' )
                    AND cte.loc_state = cte.license_state
                    AND cte.license_number = MIN(cte.license_number) OVER () AND CTE.license_number <> '' THEN
                   CAST(cte.license_issue_date AS DATE)
               ELSE
                   NULL
           END AS LicenseIssueDate,
           CAST(cte.loc_address1 AS VARCHAR(30)) AS LocAddress1,
           CAST(cte.loc_address2 AS VARCHAR(30)) AS LocAddress22,
           CAST(cte.loc_city AS VARCHAR(30)) AS LocCity,
           CAST(cte.loc_state AS VARCHAR(30)) AS LocState,
           CAST(cte.loc_zip AS VARCHAR(9)) AS LocZip,
           CAST(cte.remit_address1 AS VARCHAR(50)) AS RemitAddress1,
           CAST(cte.remit_address2 AS VARCHAR(50)) AS RemitAdress2,
           CAST(cte.remit_city AS VARCHAR(30)) AS RemitCity,
           CAST(cte.remit_state AS VARCHAR(2)) AS RemitState,
           CAST(cte.remit_zip AS VARCHAR(9)) AS RemitZip,
           cte.prov_group AS ProvGroup,
           MIN(cte.specialty) OVER (ORDER BY cte.specialty) AS Specialty,
		   CASE WHEN cte.Provider_Type IN ('Rendering', 'Solo') THEN
		   MIN(cte.specialty) OVER (ORDER BY cte.specialty) 
		   ELSE '' END AS Title,
		   
		  
           cte.main_status AS MainStatus,
           cte.sub_status AS SubStatus,
           cte.status_date AS StatusDate,
           cte.[OHCS Eff Date] AS [Client Eff Date],
           cte.sub_specialty AS SubSpecialty,
           cte.loc_county AS LocCounty,
           cte.acn_prov_id AS AcnProvID,
           cte.office_location_id AS OfficeLocationID,
           cte.loc_eff_date AS LocEffDate,
           cte.loc_term_date AS LocTermDate,
           cte.tin_eff_date AS TinEffDate,
           cte.tin_term_date AS TinTermDate,
           cte.prov_phone AS ProvPhone,
           cte.prov_fax AS ProvFax,
           cte.gender AS Gender,
           cte.client_term_date AS ClientTermDate,
           cte.cred_status_eff_date AS CredStatusEffDate,
           cte.[Social Grouping Effective Date],
           cte.[Dually Contracted]
    FROM #FirstResult cte
        LEFT JOIN bv.view_specialty_taxonomy tx
            ON cte.specialty = tx.specialty 
			WHERE COALESCE(cte.Request_Type, '') <> ''  AND cte.npi = '1114762259';
    --	(USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));
    -- OPTION  OPTION (FAST 10); (USE HINT ('DISABLE_OPTIMIZER_ROWGOAL'));
	SET STATISTICS IO OFF
	SET STATISTICS PROFILE OFF
    	

And without NPI condition. I've added SET statements. BTW, the commented HINTs are not working.

Hi Erland,

I added a comment with the code, which got immediately deleted. See if you can kindly undelete it. When I tried to added a third comment with the results I got:

Please fix the following issues to continue:

• We encountered an unexpected error. Please try again later. If this issue continues, please open a ticket at https://sitehelp.microsoft.com.

I'll try use separate code blocks.

0	1	insert [#FirstResult________________________________________________________________________________________________________0000000000C6] select *, %%bmk%% from [#FirstResult________________________________________________________________________________________________________0000000000C6]	1	1	0	NULL	NULL	NULL	NULL	2455625	NULL	NULL	NULL	208.8276	NULL	NULL	INSERT	0	NULL
0	1	  |--Parallelism(Gather Streams)	1	2	1	Parallelism	Gather Streams	NULL	NULL	2455625	0	0.7974176	9	208.8276	NULL	NULL	PLAN_ROW	1	1
0	4	       |--Index Insert(OBJECT:([tempdb].[dbo].[#FirstResult________________________________________________________________________________________________________0000000000C6]))	1	3	2	Index Insert	Insert	OBJECT:([tempdb].[dbo].[#FirstResult________________________________________________________________________________________________________0000000000C6])	NULL	2455625	14.68495	0.6139063	9	208.0302	NULL	NULL	PLAN_ROW	1	1
2455625	4	            |--Sort(ORDER BY:([#FirstResult________________________________________________________________________________________________________0000000000C6].[npi] ASC, [Bmk1000] ASC) PARTITION ID:([#FirstResult________________________________________________________________________________________________________0000000000C6].[npi]))	1	4	3	Sort	Sort	ORDER BY:([#FirstResult________________________________________________________________________________________________________0000000000C6].[npi] ASC, [Bmk1000] ASC) PARTITION ID:([#FirstResult________________________________________________________________________________________________________0000000000C6].[npi])	NULL	2455625	0.002815315	59.81595	29	192.7314	[RowRefSrc1008]	NULL	PLAN_ROW	1	1
2455625	4	                 |--Table Scan(OBJECT:([tempdb].[dbo].[#FirstResult________________________________________________________________________________________________________0000000000C6]))	1	5	4	Table Scan	Table Scan	OBJECT:([tempdb].[dbo].[#FirstResult________________________________________________________________________________________________________0000000000C6])	[Bmk1000], [#FirstResult________________________________________________________________________________________________________0000000000C6].[npi]	2455625	132.2373	0.6753165	29	132.9126	[Bmk1000], [#FirstResult________________________________________________________________________________________________________0000000000C6].[npi]	NULL	PLAN_ROW	1	1

Obviously, that extra condition will change the result, and you get back fewer rows. Is that your question?

Maybe - and I here I am guessing wildly - you are asking why some columns have different result. You mentioned something about Speciality. I see this:

 MIN(cte.specialty) OVER (ORDER BY cte.specialty) AS Specialty,

I am not sure that I understand what this means or if it makes any sense at all.

Can you please clarify what your question really is?

I posted a new comment under new thread but it doesn't appear in the threads list yet.

Basically, there are two problems. The licenses columns show populated with the filter but otherwise have no results in the whole set,

For specialty we need to get the first value alphabetically which is what my MIN code is supposed to achieve. The query with the NPI filter correctly returns 'MT', but the whole result set returns 'DC' for all records.

No, threads cannot be merged. Also, there are people from Microsoft External Staff that try to answer question in this forums, and they are served questions from an internal tool, and two different people may be served your to different questions. So posting a "part 2" only creates confusion. For this reason, I have marked your "part 2" as a duplicate.

As for the code block, I've undeleted it.

But as I said, post a simplified which highlights your actual problems.