MS SQL查询存在连续无日期间隔'ReviewSent'记录的索赔编号

Got it, let's fix this query to find exactly what you need—claim numbers that have consecutive, gap-free 'ReviewSent' notes (like your example with 10005). Your current query just pulls all 'Review Sent' notes, but doesn't check if any of them are date-consecutive. Here's how to optimize it:

Step-by-Step Solution #

First, we'll use a CTE to isolate all 'Review Sent' notes, then use the LAG() window function to compare each note's date to the previous one for the same claim. We can then filter for claims where at least two 'Review Sent' notes have no date gap between them.

WITH ReviewSentNotes AS (
    SELECT 
        cm.ClaimNum,
        a.NoteCreateDate,
        a.Notes,
        a.NoteType,
        -- Grab the date of the previous 'Review Sent' note for the same claim
        LAG(a.NoteCreateDate) OVER (PARTITION BY cm.ClaimNum ORDER BY a.NoteCreateDate) AS PreviousReviewDate
    FROM CMaster cm
    -- Use INNER JOIN instead of LEFT JOIN to only keep claims with matching notes
    INNER JOIN Note a ON cm.ClaimNum = a.PARENTREF
    INNER JOIN NoteType b ON b.ID = a.TYPECODE
    -- Filter for 'Review Sent' notes; if NoteType has a dedicated type for this, use b.NoteType = 'ReviewSent' instead (more accurate!)
    WHERE a.Body LIKE '%Review Sent%'
)
-- Get all records (or just claim numbers) where consecutive 'Review Sent' notes exist
SELECT DISTINCT
    ClaimNum,
    NoteCreateDate,
    Notes,
    NoteType
FROM ReviewSentNotes
WHERE 
    -- Check if current date and previous date are consecutive (no gap)
    -- Adjust the condition based on your definition of "gap-free":
    -- - Use DATEDIFF(day, PreviousReviewDate, NoteCreateDate) = 1 for back-to-back days
    -- - Add OR DATEDIFF(...) = 0 if same-day notes count as consecutive
    DATEDIFF(day, PreviousReviewDate, NoteCreateDate) = 1
-- If you only need the claim numbers (not the full note details), replace the SELECT with:
-- SELECT DISTINCT ClaimNum FROM ReviewSentNotes WHERE ...

Key Improvements Over Your Original Query #

• Window Function (LAG()): This lets us compare each note's date to the prior one for the same claim, which is how we detect consecutive dates.
• INNER JOIN Instead of LEFT JOIN: We don't need claims with no 'Review Sent' notes, so INNER JOIN cuts down on unnecessary rows.
• Explicit Gap Check: The DATEDIFF condition ensures we only keep claims where at least two 'Review Sent' notes have no date gap between them (matching your 10005 example, and excluding 12121 which has gaps).

Pro Tip #

If your NoteType table has a specific type ID/name for 'ReviewSent' notes (instead of relying on the Body text), replace the WHERE a.Body LIKE '%Review Sent%' with b.NoteType = 'ReviewSent'—this is faster and avoids false matches from notes that mention "Review Sent" incidentally.

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