MVC中如何对表中每行执行相同查询？（Events表更新需求）

Alright, let's work through this problem step by step. You've got an Events table with EquipApprovedDate, EquipCalDueDate, and ThemeColor fields, and you need to set ThemeColor to Green (正常), Orange (尚可), or Red (紧急) based on how much time is left until calibration is due, relative to the total window between approval and the due date.

First, let's lock in the core calculations (since your remaining days logic was cut off, I'll use standard date handling here):

• Total days: totaldays = EquipCalDueDate - EquipApprovedDate (we'll use database date functions to compute this accurately)
• Remaining days: Days left from today until EquipCalDueDate (if the due date has passed, remaining days will be negative—we'll flag this as urgent)

Step 1: Define Your Thresholds #

First, decide what qualifies for each color. Two practical approaches are:

• Percentage-based: Tie thresholds to the total calibration window (e.g., Green = >20% of total days left, Orange = 5-20% left, Red = <5% left or overdue)
• Fixed-day thresholds: Use static day counts (e.g., Green = >30 days left, Orange = 10-30 days left, Red = <10 days or overdue)

Step 2: SQL Update Query #

Below are examples for both approaches, using SQL Server syntax (adjust date functions for MySQL/PostgreSQL as noted later).

Option 1: Percentage-Based Thresholds

UPDATE Events
SET ThemeColor = 
    CASE
        -- Handle edge case where approval date equals due date (no buffer window)
        WHEN DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) = 0 THEN 'Red'
        -- Green: More than 20% of the total calibration window remains
        WHEN DATEDIFF(day, GETDATE(), EquipCalDueDate) > 0.2 * DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) THEN 'Green'
        -- Orange: Between 5% and 20% of the total window remains
        WHEN DATEDIFF(day, GETDATE(), EquipCalDueDate) BETWEEN 0.05 * DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) AND 0.2 * DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) THEN 'Orange'
        -- Red: Less than 5% remaining, or already overdue
        ELSE 'Red'
    END;

Option 2: Fixed-Day Thresholds

UPDATE Events
SET ThemeColor = 
    CASE
        -- Green: 30+ days remaining before calibration is due
        WHEN DATEDIFF(day, GETDATE(), EquipCalDueDate) > 30 THEN 'Green'
        -- Orange: 10-30 days remaining
        WHEN DATEDIFF(day, GETDATE(), EquipCalDueDate) BETWEEN 10 AND 30 THEN 'Orange'
        -- Red: <10 days left, or already past due
        ELSE 'Red'
    END;

Notes for Other Databases #

• MySQL: Replace GETDATE() with CURDATE(), and use DATEDIFF(end_date, start_date) (MySQL's DATEDIFF takes the end date first)
• PostgreSQL: Use CURRENT_DATE instead of GETDATE(), and DATE_PART('day', EquipCalDueDate - CURRENT_DATE) to calculate remaining days

Test First! #

Before running a full update, validate the logic with a SELECT query to ensure results match your expectations:

SELECT 
    EquipApprovedDate,
    EquipCalDueDate,
    DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) AS totaldays,
    DATEDIFF(day, GETDATE(), EquipCalDueDate) AS remaining_days,
    CASE
        WHEN DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) = 0 THEN 'Red'
        WHEN DATEDIFF(day, GETDATE(), EquipCalDueDate) > 0.2 * DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) THEN 'Green'
        WHEN DATEDIFF(day, GETDATE(), EquipCalDueDate) BETWEEN 0.05 * DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) AND 0.2 * DATEDIFF(day, EquipApprovedDate, EquipCalDueDate) THEN 'Orange'
        ELSE 'Red'
    END AS calculated_ThemeColor
FROM Events;

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