存储过程优化需求：双运营机器日报的misfire计数展示逻辑调整

Alright, let's fix that annoying misfire report issue and add the missing "no data" handling. Here's a step-by-step solution tailored to your stored procedure:

First, we need to make sure the report explicitly tells users when there's no data for the specified date. We'll add an early check at the start of the procedure—if no records exist, we build a simple "No results found" report and exit immediately.

The original problem was an all-or-nothing check for misfires across both machines. Instead, we'll evaluate each machine's misfire status individually, then only append stats for machines that actually have non-zero misfire counts.

Full Optimized Stored Procedure Example #

CREATE OR ALTER PROCEDURE SendDailyOpsMachineReport
    @TargetReportDate DATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Variables to hold report content and machine misfire statuses
    DECLARE @FinalReport NVARCHAR(MAX) = '';
    DECLARE @Machine1HasMisfires BIT = 0;
    DECLARE @Machine2HasMisfires BIT = 0;
    DECLARE @Machine1MisfireDetails NVARCHAR(MAX) = '';
    DECLARE @Machine2MisfireDetails NVARCHAR(MAX) = '';

    -- Step 1: Check if any data exists for the target date
    IF NOT EXISTS (
        SELECT 1 
        FROM OpsMachineDailyMetrics 
        WHERE ReportDate = @TargetReportDate
    )
    BEGIN
        -- Build the "no data" report
        @FinalReport = 'Daily Operations Report - ' + CONVERT(VARCHAR(10), @TargetReportDate, 101) + CHAR(13) + CHAR(10)
                     + '=================================' + CHAR(13) + CHAR(10)
                     + '*No results found for the specified date.*';
        
        -- Call your existing report-sending function/procedure here
        -- EXEC DispatchReport @FinalReport;
        RETURN;
    END

    -- Step 2: Build the base report content (your existing non-misfire stats)
    @FinalReport = 'Daily Operations Report - ' + CONVERT(VARCHAR(10), @TargetReportDate, 101) + CHAR(13) + CHAR(10)
                 + '=================================' + CHAR(13) + CHAR(10)
                 + -- Insert your existing base report content here (e.g., uptime, throughput)
                 + CHAR(13) + CHAR(10);

    -- Step 3: Fetch misfire stats for each machine separately
    -- Get Machine 1 misfire data
    SELECT 
        @Machine1HasMisfires = CASE WHEN MisfireCount > 0 THEN 1 ELSE 0 END,
        @Machine1MisfireDetails = 'Machine 1 Misfire Statistics:' + CHAR(13) + CHAR(10)
                                 + '- Total Misfires: ' + CAST(MisfireCount AS VARCHAR) + CHAR(13) + CHAR(10)
                                 + '- Last Misfire Time: ' + CONVERT(VARCHAR(20), LastMisfireTimestamp, 100) + CHAR(13) + CHAR(10)
                                 + CHAR(13) + CHAR(10)
    FROM OpsMachineDailyMetrics
    WHERE ReportDate = @TargetReportDate AND MachineIdentifier = 'OP_MACHINE_01';

    -- Get Machine 2 misfire data
    SELECT 
        @Machine2HasMisfires = CASE WHEN MisfireCount > 0 THEN 1 ELSE 0 END,
        @Machine2MisfireDetails = 'Machine 2 Misfire Statistics:' + CHAR(13) + CHAR(10)
                                 + '- Total Misfires: ' + CAST(MisfireCount AS VARCHAR) + CHAR(13) + CHAR(10)
                                 + '- Last Misfire Time: ' + CONVERT(VARCHAR(20), LastMisfireTimestamp, 100) + CHAR(13) + CHAR(10)
                                 + CHAR(13) + CHAR(10)
    FROM OpsMachineDailyMetrics
    WHERE ReportDate = @TargetReportDate AND MachineIdentifier = 'OP_MACHINE_02';

    -- Step 4: Append only relevant misfire stats to the report
    IF @Machine1HasMisfires = 1
    BEGIN
        @FinalReport += @Machine1MisfireDetails;
    END

    IF @Machine2HasMisfires = 1
    BEGIN
        @FinalReport += @Machine2MisfireDetails;
    END

    -- Optional: Add a note if neither machine has misfires
    IF @Machine1HasMisfires = 0 AND @Machine2HasMisfires = 0
    BEGIN
        @FinalReport += '*No misfire records detected for either machine.*' + CHAR(13) + CHAR(10);
    END

    -- Send the completed report
    -- EXEC DispatchReport @FinalReport;
END

Key Improvements Explained #

• Early Data Check: Avoids sending empty or incomplete reports when there's no data—users get clear feedback with "No results found".
• Per-Machine Misfire Evaluation: Each machine's misfire status is checked independently, so even if one has 0 misfires, the other's stats are still included if they're non-zero.
• Conditional Content Appending: Only adds misfire sections for machines that have actual misfire events, eliminating the original "skip everything if one is zero" bug.

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