请求协助编写用于发票数据校验的SQL SELECT语句

需求说明 #

针对同一Invoice No，汇总其下Invoice Line ID为1和2的[Collect USD]金额，与Total Invoice Amount对比：

• 相等时返回Validated
• 不相等时返回Price Discrepancy

修改后的完整查询代码 #

SET NOCOUNT ON;

DROP TABLE IF EXISTS #temp

SELECT * INTO #temp
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY ai.[Invoice No] ASC) AS [Invoice Line ID]
        ,ISNULL(ai.[Supplier Code], 'No Supplier Code Found') as [Supplier Code]
        ,ISNULL(ai.[Invoice No], 'No Invoice No Found') as [Invoice No]
        ,PARSE(ai.[Invoice Date] AS DATE USING 'en-US') as [Invoice Date]
        ,PARSE(ai.[Received Date] AS DATE USING 'en-US') as [Batch Date]
        ,REPLACE(RIGHT(CONVERT(VARCHAR(10),(PARSE(ai.[Invoice Date] AS DATE USING 'en-US')),103),7), '/', '-') as [Period]
        ,ISNULL(REPLACE(ai.[Master B/L No], '*', ''), 'No Master B/L No Found') as [Master B/L No]
        ,PARSE(ai.[On Board Date] AS DATE USING 'en-US') as [On Board Date]
        ,ISNULL(REPLACE(ai.[Ocean Vessel & Voyage], 'Ocean Vessel ', ''), 'No Ocean Vessel & Voyage Found') as [Ocean Vessel and Voyage]
        ,ISNULL(REPLACE(ai.[No of Cartons], 'CTNS', ''), 'No Cartons Found') as [No of Cartons]
        ,ISNULL(REPLACE(ai.[No of Pallets], 'PLTS', ''), 'No Pallets Found') as [No of Pallets]
        ,ISNULL(CONCAT('[',LEFT(ai.[Container No], CHARINDEX('/', ai.[Container No]) - 1),']'), 'No Container No Found') as [Container No]
        ,ISNULL(ai.[Port of Discharge], 'No Port of Discharge Found') as [Port of Discharge]
        ,ISNULL(ai.[Place of Delivery], 'No Place of Delivery Found') as [Place of Delivery]
        ,ISNULL(ai.[Final Destination], 'No Final Destination Found') as [Final Destination]
        ,CAST(CAST((REPLACE(ai.[Gross Weight (KG)], ' KGS', '')) as decimal(18,5)) as float) as [Gross Weight KG]
        ,ISNULL(REPLACE(ai.[H B/L No], '*', ''), 'No H B/L No Found') as [H BL No]
        ,ISNULL(REPLACE(ai.[Consignee], ',', ''), 'No Consignee Found') as [Consignee]
        ,ISNULL(ai.[Volume], 'No Volume Found') as [Volume]
        ,ISNULL(ai.[Item], 'No Item Found') as [Item]
        ,ISNULL(ai.[Prepaid], 'No Prepaid Found') as [Prepaid]
        ,CAST(ai.[Collect (USD)] as decimal(18,5)) as [Collect USD]
        ,CAST(ai.[Total Invoice Amount] as decimal(18,5)) as [Total Invoice Amount]
        ,CASE
            WHEN ai.[Port of Discharge] LIKE '%LONG BEACH%' 
            OR ai.[Port of Discharge] LIKE '%LOS ANGELES%' 
            OR ai.[Port of Discharge] LIKE '%TACOMA%' 
            OR ai.[Port of Discharge] LIKE '%VANCOUVER%'
            OR ai.[Port of Discharge] LIKE '%NEW YORK%'
            OR ai.[Port of Discharge] LIKE '%SEATTLE%'
            AND ai.[Final Destination] IS NULL
                THEN 'GRAND RAPIDS, MI'
                ELSE ISNULL(ai.[Final Destination], 'No Warehouse Found')
            END as [Warehouse]
        ,CASE
            WHEN ai.[Consignee] LIKE '%VIKING PRODUCTS INC.%' AND ai.[Item] LIKE '%OCEAN FREIGHT%' THEN '51-512000-VIK'
            WHEN ai.[Consignee] LIKE '%VIKING PRODUCTS DE M%' AND ai.[Item] LIKE '%OCEAN FREIGHT%' THEN '51-512050-VIK'
            WHEN ai.[Item] LIKE '%ORIGIN EXAM FEE%' OR ai.[Item] LIKE '%ISF%' THEN '51-514000-VIK'
                ELSE 'Account No Not Found'
            END as [Account No]
        FROM [DWH].[vplx].[AP_Invoice_Detail] ai
        WHERE 1=1
            AND CHARINDEX('/', [Container No]) > 0
    ) as t

SELECT 
    t.*, 
    aj.[accounting_job_no] as [Accounting Job No],
    -- 计算当前Invoice No下Line ID 1和2的Collect USD总和
    SUM(CASE WHEN t.[Invoice Line ID] IN (1,2) THEN t.[Collect USD] ELSE 0 END) 
        OVER(PARTITION BY t.[Invoice No]) AS [Line 1+2 Collect Total],
    -- 验证逻辑：对比总和与发票总金额
    CASE 
        WHEN SUM(CASE WHEN t.[Invoice Line ID] IN (1,2) THEN t.[Collect USD] ELSE 0 END) 
                OVER(PARTITION BY t.[Invoice No]) = t.[Total Invoice Amount]
            THEN 'Validated'
            ELSE 'Price Discrepancy'
    END AS [Validation Result]
FROM #temp t
LEFT JOIN [plx].[Accounting_v_Accounting_Job_e_lookup] aj
    ON (t.[Warehouse] = aj.[final_dest_one]) OR
    (t.[Warehouse] = aj.[final_dest_two]) OR
    (t.[Warehouse] = aj.[final_dest_three]) OR
    (t.[Warehouse] = aj.[final_dest_four]) OR
    (t.[Warehouse] = aj.[final_dest_five]) OR
    (t.[Warehouse] = aj.[final_dest_six]) OR
    (t.[Warehouse] = aj.[final_dest_seven])
ORDER BY t.[Invoice No] asc

关键逻辑说明 #

1. 窗口函数计算汇总值：使用SUM() OVER(PARTITION BY [Invoice No])配合CASE筛选指定行，一次性计算每个发票对应的金额总和，无需额外子查询或临时表。
2. CASE验证判断：直接将汇总值与Total Invoice Amount对比，返回对应的验证状态，逻辑清晰直观。
3. 兼容原有查询结构：所有新增逻辑嵌入最终查询中，完全保留原有数据字段与关联逻辑。

注意事项 #

• 需确保同一Invoice No下的Total Invoice Amount值一致，否则会出现同一发票不同行验证结果不同的情况。
• 若仅需发票级别的验证结果，可将汇总逻辑提前写入#temp表，或通过GROUP BY进行聚合查询。

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