SQL Server中如何根据条件为属性赋值并每日自动检查更新配送状态？

Absolutely! You have two solid approaches to handle this requirement—one that gives you real-time status without needing daily updates, and another that persists the status in your table with a scheduled job. Let’s break them down:

If you don’t strictly need the status to be a physical column in the Delivery table, creating a view is the cleanest solution. It calculates the status on-the-fly every time you query it, so you never have to worry about stale data or manual updates.

Here’s how to create the view:

CREATE VIEW vw_DeliveryStatus
AS
SELECT
    d.*,
    -- Calculate status based on your business rules
    CASE
        -- Handle null deliveryDate
        WHEN d.deliveryDate IS NULL THEN
            CASE
                WHEN GETDATE() < DATEADD(DAY, 10, o.orderDate) THEN 'in progress'
                ELSE 'late'
            END
        -- Handle non-null deliveryDate
        ELSE
            CASE
                WHEN d.deliveryDate > DATEADD(DAY, 10, o.orderDate) THEN 'late'
                ELSE 'on time'
            END
    END AS status
FROM Delivery d
JOIN [Order] o ON d.orderId = o.orderId;

Just query vw_DeliveryStatus whenever you need the latest status—it’ll always reflect the current date and your rules automatically.

If you need the status stored as a physical column in Delivery, you can use SQL Server Agent to run a daily update job. This refreshes the status to match your rules every day.

Step 1: Write the Efficient Update Query #

First, create the T-SQL that updates the status. To avoid unnecessary full-table updates, add a WHERE clause to only target rows where the status might have changed:

UPDATE d
SET status = 
    CASE
        WHEN d.deliveryDate IS NULL THEN
            CASE
                WHEN GETDATE() < DATEADD(DAY, 10, o.orderDate) THEN 'in progress'
                ELSE 'late'
            END
        ELSE
            CASE
                WHEN d.deliveryDate > DATEADD(DAY, 10, o.orderDate) THEN 'late'
                ELSE 'on time'
            END
    END
FROM Delivery d
JOIN [Order] o ON d.orderId = o.orderId
WHERE
    -- Only update rows where status doesn't match the calculated value
    (d.deliveryDate IS NULL AND 
     d.status != CASE WHEN GETDATE() < DATEADD(DAY, 10, o.orderDate) THEN 'in progress' ELSE 'late' END)
    OR
    (d.deliveryDate IS NOT NULL AND 
     d.status != CASE WHEN d.deliveryDate > DATEADD(DAY, 10, o.orderDate) THEN 'late' ELSE 'on time' END);

Step 2: Set Up the Daily Job in SQL Server Agent #

1. Open SQL Server Management Studio (SSMS) and expand the SQL Server Agent node in Object Explorer.
2. Right-click Jobs and select New Job....
3. On the General tab, name your job (e.g., "Daily Delivery Status Update").
4. Go to the Steps tab, click New..., and configure the step:
   • Step name: e.g., "Update Status Column"
   • Type: Transact-SQL (T-SQL)
   • Database: Select your target database
   • Paste the update query into the Command box
5. Switch to the Schedules tab, click New..., and set up a daily schedule:
   • Schedule name: e.g., "Daily Off-Peak Run"
   • Frequency: Daily
   • Set a time (like 2 AM) when your system is least busy
6. Save the job—it will now run automatically every day to refresh the status column.

Quick Notes #

• Ensure the SQL Server Agent service is running (it’s required for scheduled jobs to execute).
• GETDATE() uses the server’s system time—double-check it aligns with your business’s time zone.
• For large datasets, the optimized update query (with the WHERE clause) will run much faster than updating every row in the table.

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