T-SQL多连接查询需求修改：补充空值与日期逻辑

Alright, let's refine your working T-SQL multi-join query to meet those new requirements. I'll break down the changes and show you the optimized code with clear explanations.

Key Changes to Implement #

We need to address two core requirements:

1. Adjust Test Date logic: Replace NULL values with the current date, keep existing values otherwise.
2. Conditional nulling for specific columns: Set Entered By, TIN, and Tested By to NULL only when Test Date is NULL AND Date Sampled is more than 29 days past the current date.

Optimized Query Code #

First, here's a straightforward implementation that directly applies the logic to each field:

SELECT
    -- Handle Test Date: Use current date if NULL
    ISNULL(t.[Test Date], GETDATE()) AS [Test Date],
    -- Conditionally null Entered By
    CASE 
        WHEN t.[Test Date] IS NULL AND DATEDIFF(day, s.[Date Sampled], GETDATE()) > 29 
        THEN NULL 
        ELSE t.[Entered By] 
    END AS [Entered By],
    -- Conditionally null TIN
    CASE 
        WHEN t.[Test Date] IS NULL AND DATEDIFF(day, s.[Date Sampled], GETDATE()) > 29 
        THEN NULL 
        ELSE t.[TIN] 
    END AS [TIN],
    -- Conditionally null Tested By
    CASE 
        WHEN t.[Test Date] IS NULL AND DATEDIFF(day, s.[Date Sampled], GETDATE()) > 29 
        THEN NULL 
        ELSE t.[Tested By] 
    END AS [Tested By],
    -- Keep all your existing columns/joins below
    t.OriginalColumn1,
    s.OriginalColumn2,
    j.OriginalColumn3
FROM YourMainTable t
JOIN SamplingTable s ON t.RecordID = s.RecordID
JOIN OtherJoinedTable j ON t.RecordID = j.RecordID
-- Add your existing WHERE/HAVING/ORDER BY clauses here

Cleaner Version (Reduce Repetition) #

If you want to avoid repeating the same condition across multiple columns, use CROSS APPLY to precompute the condition once. This makes the code easier to maintain if the condition ever changes:

SELECT
    ISNULL(t.[Test Date], GETDATE()) AS [Test Date],
    -- Reuse the precomputed condition for all columns
    CASE WHEN cond.ShouldNullColumns THEN NULL ELSE t.[Entered By] END AS [Entered By],
    CASE WHEN cond.ShouldNullColumns THEN NULL ELSE t.[TIN] END AS [TIN],
    CASE WHEN cond.ShouldNullColumns THEN NULL ELSE t.[Tested By] END AS [Tested By],
    -- Keep your existing columns
    t.OriginalColumn1,
    s.OriginalColumn2,
    j.OriginalColumn3
FROM YourMainTable t
JOIN SamplingTable s ON t.RecordID = s.RecordID
JOIN OtherJoinedTable j ON t.RecordID = j.RecordID
-- Precompute the null condition once
CROSS APPLY (
    SELECT 
        CASE 
            WHEN t.[Test Date] IS NULL AND DATEDIFF(day, s.[Date Sampled], GETDATE()) > 29 
            THEN 1 
            ELSE 0 
        END AS ShouldNullColumns
) cond
-- Add your existing clauses here

Important Notes #

• Date Precision: If you only need the date part (no time), replace GETDATE() with CAST(GETDATE() AS DATE) to avoid including time values.
• Testing: Make sure to validate these scenarios to ensure correctness:
  • Test Date is NULL and Date Sampled is >29 days old (columns should show NULL)
  • Test Date is NULL but Date Sampled is ≤29 days old (columns retain original values)
  • Test Date has a valid value (all columns behave as before, Test Date stays unchanged)

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