SQL Server查询需求：基于Top100最新结果构建条件判断逻辑

Let's break down how to build this query exactly as you need it. The core idea is to first grab the latest 100 records, then validate if all of them meet your two conditions, and output the specified message if they do.

Step-by-Step Query Implementation #

Here's a complete, tested query you can use (just replace YourTableName with your actual table name):

WITH Latest100Reports AS (
    -- First, get the 100 most recent records sorted by Create_Date
    SELECT TOP 100
        Report_Status,
        Error_Message
    FROM YourTableName
    ORDER BY Create_Date DESC
)
-- Now check if all 100 records meet the required conditions
SELECT 
    CASE 
        -- Count how many records fail to meet the criteria
        WHEN COUNT(CASE 
                     WHEN Report_Status <> 'Failed' 
                          OR Error_Message LIKE 'Placement is missing%' 
                     THEN 1 
                   END) = 0
        THEN 'Potential service fail...'
        ELSE 'No potential service failure detected' -- Optional default message
    END AS StatusMessage
FROM Latest100Reports;

How This Works #

• CTE (Latest100Reports): This common table expression pulls the 100 newest records by sorting Create_Date in descending order. If multiple records have the same latest Create_Date, you can add a secondary sort column (like a unique report ID) to ensure consistent results: ORDER BY Create_Date DESC, ReportID DESC.
• Main Query: The CASE statement inside COUNT tallies any records that don't match your rules (either Report_Status isn't 'Failed', or Error_Message contains 'Placement is missing%'). If that count is 0, every record in the top 100 meets your conditions, so we output the "Potential service fail..." message.

Notes #

• Make sure to replace YourTableName with the actual name of your table containing the Report_Status, Error_Message, and Create_Date columns.
• If you don't want a default message when the conditions aren't met, you can adjust the ELSE clause (e.g., return NULL instead).

内容的提问来源于stack exchange，提问作者Jeremiah Williams