SQL查询中新增@PaymentStatusID_DV变量并过滤NULL值的位置咨询

Got it, let's work through this SQL modification properly. The key here is that your @PaymentStatusID_DV value depends on row-specific fields (like c.ClientID, l.LeadID, etc.), so we can't just assign it as a global variable once—we need to calculate it for every row and filter out any where it returns NULL. Here's the adjusted query with explanations:

Modified SQL Query #

DECLARE @SettlementDate DATE, @PaymentStatusID_DV VARCHAR(2000), @ClientID INT = 384

-- Keep your existing logic to get the settlement date
SELECT @SettlementDate = dbo.fnAddWorkingDays ( 
    dbo.fnGetNextWorkingDate (CONVERT(DATE,GETDATE()),0) ,
    cdv.ValueInt + 1 
) 
FROM ClientDetailValues cdv WITH (NOLOCK) 
WHERE cdv.DetailFieldID=170226 AND cdv.ClientID=@ClientID

-- Main query with PaymentStatusID_DV calculation and NULL filtering
SELECT 
    c.CustomerID,
    ca.CaseID,
    ca.LatestInProcessLeadEventID [LeadEventID],
    m.MatterID,
    COUNT(m.MatterID)
FROM Customers c WITH (NOLOCK) 
INNER JOIN Lead l WITH (NOLOCK) ON c.CustomerID=l.CustomerID 
INNER JOIN Cases ca WITH (NOLOCK) ON l.LeadID=ca.LeadID 
INNER JOIN Matter m WITH (NOLOCK) ON ca.CaseID=m.CaseID 
LEFT JOIN MatterDetailValues suspended WITH (NOLOCK) ON m.MatterID=suspended.MatterID AND suspended.DetailFieldID=175275 
INNER JOIN LeadTypeRelationship ltr WITH (NOLOCK) ON ltr.ToMatterID=m.MatterID AND ltr.FromLeadTypeID=1492 AND ltr.ToLeadTypeID=1493 
INNER JOIN Matter pam WITH (NOLOCK) ON ltr.FromMatterID=pam.MatterID 
INNER JOIN CustomerPaymentSchedule cps WITH (NOLOCK) ON cps.CustomerID = c.CustomerID AND cps.WhenCreated > '2017-09-01' 
INNER JOIN Account a WITH (NOLOCK) ON a.AccountID = cps.AccountID 
-- Use CROSS APPLY to calculate the PaymentStatusID_DV for each row
CROSS APPLY (
    SELECT dbo.fnGetSimpleDvByThirdPartyField(
        c.ClientID, c.CustomerID, l.LeadID, ca.CaseID, m.MatterID, 1490, 4370
    ) AS PaymentStatusValue
) AS psdv
WHERE 
    c.Test=0 
    AND c.ClientID=@ClientID 
    AND NOT EXISTS ( 
        SELECT * FROM LeadEvent le WITH (NOLOCK) 
        WHERE le.CaseID=ca.CaseID AND le.EventDeleted=0 AND le.EventTypeID=155198 
    ) 
    AND (suspended.ValueInt <> 5144 OR suspended.ValueInt IS NULL) 
    AND cps.CustomerLedgerID IS NULL 
    AND cps.ActualCollectionDate <= @SettlementDate 
    AND cps.PaymentGross < 0 
    AND a.AccountTypeID=1 
    -- Add filter to exclude rows where PaymentStatusID_DV is NULL
    AND psdv.PaymentStatusValue IS NOT NULL
GROUP BY 
    c.CustomerID,
    ca.CaseID,
    ca.LatestInProcessLeadEventID,
    m.MatterID

Key Changes Explained #

• Fixed Variable Declaration: I added INT to @ClientID = 384 (adjust the type if your ClientID uses a different data type) since untyped declarations can cause unexpected issues.
• Row-Specific Calculation: Instead of assigning a single global @PaymentStatusID_DV value, we use CROSS APPLY to run the function for every row. This ensures we get the correct value tailored to each record in your result set.
• NULL Filter: The AND psdv.PaymentStatusValue IS NOT NULL condition in the WHERE clause removes any rows where the function returns NULL, exactly as you requested.
• Clean GROUP BY: Since we're not returning the PaymentStatusID_DV value in the final output, we don't need to include it in the GROUP BY clause. If you want to include this value in your results, just add it to both the SELECT and GROUP BY sections.

If you had initially intended to use a global variable (though that wouldn't make logical sense for row-specific data), assigning it after the main query would only capture the value from the last row—this wouldn't help with filtering. The CROSS APPLY approach is the correct way to handle this requirement.

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