Why is some of the Where clause being ignored?

Question

I have this table:

CREATE TABLE [dbo].[Handshake]
(
    [Report Year] [varchar](100) NULL,
    [Status] [varchar](100) NULL,
    [Update Time] [datetime] NULL,
    [Process Time] [datetime] NULL
) ON [PRIMARY]
GO

Some sample data:

Report Year Status       Update Time                Process Time
----------------------------------------------------------------
Test        Loading      2020-09-22 12:53:41.417    NULL
2020 1+7    Loaded       2020-09-22 12:46:41.417    NULL
2020 2+7    Loaded       2020-09-21 21:18:25.130    NULL

I have the following procedure:

DECLARE
    @ReportYearCmd VARCHAR(1000),
    @CosCountCmd VARCHAR(1000),
    @FranchiseCountCmd AS VARCHAR(1000),
    @ProductCountCmd AS VARCHAR(1000);

WITH Validations AS 
(
    SELECT TOP 1 * 
    FROM [Handshake] 
    WHERE [Status] <> 'Loading' 
      AND [Update Time] = (SELECT MAX([Update Time]) FROM Handshake)
)
UPDATE Validations 
SET @ReportYearCmd = CASE 
                        WHEN Report_Year_Count = 0 
                           THEN NULL 
                           ELSE 'SELECT DISTINCT [Report Year] AS [Report Year] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] );'
                     END,
    @CosCountCmd = CASE 
                      WHEN COS_Count = 0 
                         THEN NULL 
                         ELSE 'SELECT DISTINCT [Country Code] AS [COS - Country Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] );' 
                   END,
    @FranchiseCountCmd = CASE WHEN  Franchise_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Franchise - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] );' 
    END,
    @ProductCountCmd = CASE WHEN Product_Count = 0 THEN NULL 
        ELSE 'SELECT DISTINCT [Style Code] AS [Product - Style Code] FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] );' 
    END,
    [Status] = CASE
        WHEN ( Report_Year_Count = 0 AND COS_Count = 0 AND Franchise_Count = 0 AND Product_Count = 0 ) THEN 'Good'
        ELSE 'Rejects'
    END
FROM 
    [Validations] 
OUTER APPLY 
    (SELECT 
         ISNULL((SELECT COUNT(*) FROM [Fact] 
                 WHERE NOT EXISTS (SELECT * FROM [Report Year] WHERE [Report Year].[Report Year] = [Fact].[Report Year] ) ), 0 ) AS [Report_Year_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [COS] WHERE [COS].[Country Code] = [Fact].[Country Code] ) ), 0 ) AS [COS_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Franchise] WHERE [Franchise].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Franchise_Count],
        ISNULL( ( SELECT COUNT(*) FROM [Fact] WHERE NOT EXISTS ( SELECT * FROM [Product] WHERE [Product].[Style Code] = [Fact].[Style Code] ) ), 0 ) AS [Product_Count]

) AS [ValidationCounts];

The procedure works only if I don't include this part in the Validations SELECT statement: ...WHERE [Status] <> 'Loading'..., and therefore picks the most recent update time record, e.g.

Test        Loading      2020-09-22 12:53:41.417    NULL

When I do include ...WHERE [Status] <> 'Loading'..., nothing happens!

Expectation: The query should pick up this record:

2020 1+7    Loaded       2020-09-22 12:46:41.417    NULL

But it doesn't...why?

Answer 1

If you want the latest non-loading status, there is no need for a subquery. You can just use filter on non-loading rows with a where clause, then order by descending date, and retain tne top (1) row only:

WITH validations AS (
    SELECT TOP (1) * 
    FROM [Handshake] 
    WHERE [Status] <> 'Loading' 
    ORDER BY [Update Time] DESC
)
UPDATE validations ...