使用位运算查找T-SQL中@Week_DataTable的缺失日期

Alright, let's break down how to solve this problem of finding missing dates for each ID1/ID2 pair using bitwise operations and your lookup table. Here's a step-by-step approach with code examples:

First, let's confirm the core assumption we're working with: we treat the Code field as a bitmask where each bit corresponds to a Day_Enum value. For example:

• Day_Enum = 1 maps to the 0th bit (value = 2^0 = 1)
• Day_Enum = 2 maps to the 1st bit (value = 2^1 = 2)
• And so on. A bit set to 1 means the date exists in @Week_DataTable for that ID1/ID2 pair; 0 means it's missing.

First, we need to create a list of every possible ID1/ID2 + valid Day_Enum combination that should exist. This is done by taking the unique ID pairs from your data table and cross-joining them with the valid days from your lookup table.

Next, we'll compare each expected combination against the existing Code values in @Week_DataTable using the bitwise AND (&) operator. If the result is 0, the corresponding bit isn't set—meaning that date is missing for the ID pair.

Full T-SQL Code Example #

-- CTE to get all unique ID1/ID2 pairs from your data table
WITH UniqueIDPairs AS (
    SELECT DISTINCT ID1, ID2
    FROM @Week_DataTable
),
-- CTE to get only valid days from the lookup table
ValidDayEnums AS (
    SELECT Day_Enum
    FROM @Days
    WHERE [Validate] = 1
),
-- CTE to generate every expected ID1/ID2 + Day_Enum combination
ExpectedCombinations AS (
    SELECT 
        u.ID1, 
        u.ID2, 
        d.Day_Enum
    FROM UniqueIDPairs u
    CROSS JOIN ValidDayEnums d
),
-- CTE to get the aggregated Code value per ID1/ID2 pair
-- (Use BIT_OR if multiple rows exist for the same pair; adjust if needed)
ExistingBitmasks AS (
    SELECT 
        ID1, 
        ID2, 
        BIT_OR(Code) AS CombinedCode -- Merges bits from all rows for the pair
    FROM @Week_DataTable
    GROUP BY ID1, ID2
)
-- Final query to find missing dates
SELECT 
    ec.ID1, 
    ec.ID2, 
    ec.Day_Enum AS Missing_Day_Enum
FROM ExpectedCombinations ec
JOIN ExistingBitmasks eb 
    ON ec.ID1 = eb.ID1 
    AND ec.ID2 = eb.ID2
-- Check if the bit for Day_Enum is NOT set in CombinedCode
WHERE (eb.CombinedCode & POWER(2, ec.Day_Enum - 1)) = 0;

Key Adjustments to Fit Your Data #

• If Day_Enum starts at 0: Change ec.Day_Enum - 1 to just ec.Day_Enum in the POWER function.
• If each ID1/ID2 has only one Code row: Replace BIT_OR(Code) with just Code in the ExistingBitmasks CTE.
• Need actual date values: If @Days includes a concrete date column (e.g., [Date]), add it to the ValidDayEnums CTE and include it in the final select to see the missing date directly.

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