税务差异查询因Tax Master Table更新返回2行而非1行的问题

Got it, let's break down this problem and fix that duplicate record issue in your tax difference query. Here's what's happening and how to resolve it:

Root Cause Analysis #

Your issue stems from the Full Outer Join logic in the "calculated and invoiced" union subquery. When the tax sequence updates from 1 to 2 after invoicing:

• The invoiced tax record is tied to the old tax_sequence = 1
• The calculated tax record uses the new tax_sequence = 2
  Since you're joining directly on tax_sequence, the join can't match these two records—so it returns two separate rows (one with calculated tax, one with invoiced tax) instead of merging them to show the difference.

Solution Approach #

Instead of joining directly on the current tax_sequence, you need to match the invoiced tax to the exact tax version that was active when the invoice was created. This requires leveraging date-based versioning from your Tax Master Table (assuming it tracks effective dates for tax changes—if not, you'll need a tax history table to track sequence updates over time).

Modified SQL Snippet for the "Calculated & Invoiced" Subquery #

Replace your existing second union subquery with this adjusted version, which first maps each invoice to the correct tax sequence active on the invoice date:

-- 2. Calculated AND Invoiced Tax (corrected to avoid duplicates)
SELECT
    tax.tax_id,
    tax.tax_name,
    COALESCE(calc.calculated_tax_amount, 0) AS calculated_tax,
    COALESCE(inv.invoiced_tax_amount, 0) AS invoiced_tax,
    (COALESCE(calc.calculated_tax_amount, 0) - COALESCE(inv.invoiced_tax_amount, 0)) AS tax_difference
FROM
    -- Subquery to get invoiced tax linked to its active tax sequence at invoice time
    (
        SELECT
            it.tax_id,
            it.invoiced_tax_amount,
            tm.tax_sequence AS invoiced_tax_sequence,
            tm.tax_name
        FROM
            invoice_tax it
        JOIN
            tax_master tm 
            ON it.tax_id = tm.tax_id
            -- Match the tax version active when the invoice was issued
            AND it.invoice_date BETWEEN tm.effective_start_date 
            AND COALESCE(tm.effective_end_date, GETDATE()) -- Use current date if no end date
    ) inv
FULL OUTER JOIN
    calculated_tax calc
    -- Join on tax ID AND the sequence that was active at invoicing time
    ON inv.tax_id = calc.tax_id
    AND inv.invoiced_tax_sequence = calc.tax_sequence
WHERE
    -- Ensure we only include records where both calculated and invoiced amounts exist
    calc.calculated_tax_amount IS NOT NULL
    AND inv.invoiced_tax_amount IS NOT NULL

Key Fixes in This Code #

• Date-based Tax Version Matching: The inner subquery links each invoice to the exact tax_sequence that was valid when the invoice was created, not the current updated sequence.
• Correct Join Logic: Now the Full Outer Join matches on both tax_id and the historical tax_sequence from invoicing, so it merges the calculated and invoiced amounts into a single row instead of returning duplicates.

Additional Recommendations #

1. Validate Tax Master Schema: If your Tax Master Table doesn't have effective_start_date and effective_end_date fields, you'll need to add them to track when each tax sequence version becomes active/inactive.
2. Test Edge Cases: Verify with other tax types that have sequence updates, and ensure the other two union subqueries (calculated but not invoiced, invoiced but not calculated) still work as expected.
3. Simplify if Possible: If you only care about matching the most recent tax sequence for a given invoice date, you can use a ROW_NUMBER() window function to pick the correct tax version if multiple entries exist for a tax ID on the same date.

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