基于GRN与PO凭证分录的GRN及AP发票总值分组问题求助

Hey there, let's work through this grouping problem you're having with GRN and AP invoice totals. The core issue here is that linking GRN and AP line items directly is creating duplicate rows, which causes your sums to double (or multiply incorrectly) when you aggregate. Let's break down how to fix this.

Why Your Current CTE is Failing #

When you join GRN and AP entries first before aggregating, you're creating a Cartesian product: each GRN line gets paired with multiple AP lines, and vice versa. This means the same GRN values get counted multiple times in your final sum, leading to inflated totals that don't match reality.

Step-by-Step Solution #

The fix is to aggregate GRN and AP totals separately first (removing duplicates in each dataset) before joining them. Here's how to do it with CTEs, tailored to your data structure:

1. Clean & Aggregate GRN Data #

We'll group GRN entries by their unique identifiers (DocNum + BaseLine) to eliminate duplicate rows, then sum the values:

WITH Unique_GRN AS (
    -- Get unique GRN lines by grouping on DocNum and BaseLine
    SELECT 
        DocNum,
        BaseLine,
        MAX(DocTotal) AS GRN_DocTotal, -- DocTotal is same per DocNum, so MAX works
        MAX(LineTotal) AS GRN_LineTotal, -- Same LineTotal per BaseLine
        MAX(VatSum) AS GRN_VatSum -- Same VatSum per BaseLine
    FROM your_table_name
    GROUP BY DocNum, BaseLine
),
GRN_Summary AS (
    -- Calculate final GRN totals per document
    SELECT 
        DocNum,
        SUM(GRN_LineTotal) AS GRN_Line_Total,
        SUM(GRN_VatSum) AS GRN_Vat_Sum,
        MAX(GRN_DocTotal) AS GRN_Total
    FROM Unique_GRN
    GROUP BY DocNum
)

2. Clean & Aggregate AP Data #

Do the same for AP entries, using their unique identifier (DocNum + BaseEntry):

, Unique_AP AS (
    -- Get unique AP lines by grouping on DocNum and BaseEntry
    SELECT 
        DocNum,
        BaseEntry,
        SUM(LineTotal) AS AP_LineTotal -- Sum lines that belong to the same AP entry
    FROM your_table_name
    GROUP BY DocNum, BaseEntry
),
AP_Summary AS (
    -- Calculate final AP totals per document
    SELECT 
        DocNum,
        SUM(AP_LineTotal) AS AP_Line_Total
    FROM Unique_AP
    GROUP BY DocNum
)

3. Join the Summaries #

Now that we have clean, aggregated totals for GRN and AP, we can join them safely without duplicates:

-- Final combined results
SELECT 
    gs.GRN_Total AS GRN_Total,
    gs.GRN_Vat_Sum AS GRN_Vat_Sum,
    aps.AP_Line_Total AS AP_Total
FROM GRN_Summary gs
INNER JOIN AP_Summary aps ON gs.DocNum = aps.DocNum;

Matching Your Expected Results #

For your sample data, this query will output:

If you need the final total (matching DocTotal) for all three columns, you can adjust the final select to use gs.GRN_Total for all, since DocTotal represents the full inclusive value for the document.

Key Takeaway #

Always aggregate your datasets before joining them when dealing with one-to-many relationships. This avoids the duplicate row problem that's inflating your sums.

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