sql - 删除给定键总和为零的行

Question

我有一个查询，该查询将导致在我们的SSRS 2008 R2服务器上创建客户账单。 SQL Server实例也是2008 R2。查询很大，出于安全原因等原因，我不想发布整个内容。

我需要使用以下示例数据，是从结果集中删除具有73.19和-73.19的两行。因此，如果两行在LineBalance列中具有相同的绝对值，并且它们的总和为0，并且如果它们在REF1列中具有相同的值，则应将它们从结果集中删除。 REF1 = 14598和平衡线为281.47的行仍应返回结果集中，而REF1 = 14598的下面其他两行则不应返回。

这样做的目的是“隐藏”客户的会计错误及其更正。我的意思是“隐藏”，而不是在他们收到的帐单上显示出来。在这里发生的是，该客户在应收账单281.47时被误收费73.19。因此，我们的AR部门。将73.19退还到他们的帐户中，并向他们收取了281.47的正确金额。如您所见，它们都具有相同的REF1值。

Answer 1

大多数应收账款/开票系统都将“贷项通知单”(负金额)与现金类似，在这种情况下，-73.19将应用到73.19 LineBalance上，就像客户已经支付了该金额一样，导致余额为$ 0。

选项1:

您在此系统中处理现金收入和申请吗？如果是这样，您也许可以从那些现金应用程序表中提取数据，以显示SysInvNum 3344296和3341758之间的关系。

选项2:

我假设PayAdjust列用于减少客户付款后的余额，并且LineBalance是一个计算的列，它是Charges + PayAdjust。

在大多数情况下，AR部门将负责将贷项凭证应用于未结清的发票，以便PayAdjust列在两行之间净赚$ 0，这将导致LineBalance在每行中也均为$ 0。 2行。对于正在使用的系统，这可能只是一个培训问题。

这将导致有问题的3行看起来像这样，所以您没有问题，您只需在AR部门中添加where LineBalance <> 0到查询中就可以排除这些行，因为AR部门(将功劳归功于此，因此知道该问题的答案)明确说明了该抵免额适用于哪种LineBalance:

选项2首选数据结构:

SysInvNum   REF1        Charges               PayAdjust             LineBalance
----------- ----------- --------------------- --------------------- ---------------------
3344298     14598       281.47                0.00                  281.47
3344296     14598       -73.19                73.19                 0.00
3341758     14598       73.19                 -73.19                0.00

选项3:

如果没有来自选项1或2的数据，您将有许多假设，并冒着无意间隐藏错误行的风险。

话虽如此，这是一个尝试执行您所要查询的查询，但是我强烈建议您与AR部门进行核对，以查看它们是否可以为这些记录更新“PayAdjust”。

我添加了一些可能会导致问题的方案的测试案例，但这可能无法涵盖所有​​基础。

对于相同的REF1和相同的DueDate，此查询将仅隐藏为正值找到一个不同的匹配负值的行。它还可以确保原始的费用发票ID在贷方之前，因为可以假定在实际费用之前不会发生贷方(测试案例6仍显示两行，因为贷方具有在费用之前发生的SysInvNum )。如果每个 REF1, DueDate, and LineBalance找到多个匹配项，则它将不会隐藏相应的费用和信用额度(测试案例2和4)。测试用例3的总和为0，但是它仍然显示所有3行，因为LineBalance值不完全匹配。这些都是我处理极端情况的假设，因此可以根据需要进行调整。

CREATE TABLE #SysInvTable (SysInvNum int not null primary key, REF1 int, Charges money, PayAdjust money, LineBalance as Charges + PayAdjust, DueDate date, REF2 int, Remark varchar(50), REM varchar(50));

INSERT INTO #SysInvTable(SysInvNum, REF1, Charges, PayAdjust, DueDate, Remark) 
VALUES
    --.....................................
    --Your test case
      (3344298, 14598, 281.47, 0, '2014-12-08','Your original test case. This one should stay.')
    , (3344296, 14598, -73.19, 0, '2014-12-08',null)
    , (3341758, 14598, 73.19, 0, '2014-12-08',null)
    --.....................................
    --Test case 2: How do you match these up? 
    , (2001, 2, 73.19, 0, '2015-01-06','Charge 2.1')
    , (2002, 2, 73.19, 0, '2015-01-06','Charge 2.2')
    , (2003, 2, 73.19, 0, '2015-01-06','Charge 2.3')
    , (2004, 2, -73.19, 0, '2015-01-06','Credit for charge 2.3')
    , (2005, 2, -73.19, 0, '2015-01-06','Credit for charge 2.1') 
    --.....................................
    --Test case 3
    , (3001, 3, 73.19, 0, '2015-01-06','Charge 3.1')
    , (3002, 3, 73.19, 0, '2015-01-06','Charge 3.2')
    , (3003, 3, -146.38, 0, '2015-01-06','Credit for charges 3.1 and 3.2') 
    --.....................................
    --Test case 4: Do you hide 4001 or 4002? 
    , (4001, 4, 73.19, 0, '2015-01-06','Cable')
    , (4002, 4, 73.19, 0, '2015-01-06','Internet')
    , (4003, 4, -73.19, 0, '2015-01-06','Misc Credit')
    --.....................................
    --Test case 5: remove all lines except the first
    , (5000, 5, 9.99, 0, '2015-01-06','Charge 5.0 (Should stay)')
    , (5001, 5, 11.11, 0, '2015-01-06','Charge 5.1')
    , (5002, 5, 22.22, 0, '2015-01-06','Charge 5.2')
    , (5003, 5, 33.33, 0, '2015-01-06','Charge 5.3')
    , (5004, 5, -11.11, 0, '2015-01-06','Credit for charge 5.1')
    , (5005, 5, -33.33, 0, '2015-01-06','Credit for charge 5.3') 
    , (5006, 5, -22.22, 0, '2015-01-06','Credit for charge 5.2') 
    --.....................................
    --Test case 6: credit occurs before charge, so keep both
    , (6000, 6, -73.19, 0, '2015-01-06','Credit occurs before charge')
    , (6001, 6, 73.19, 0, '2015-01-06','Charge 6.1')
;

SELECT i.* 
FROM #SysInvTable i
WHERE i.SysInvNum not in 
(
    SELECT IngoreInvNum = case when c.N = 1 then max(t.SysInvNum) else min(t2.SysInvNum) end
    FROM #SysInvTable t
    INNER JOIN #SysInvTable t2 
        ON t.ref1 = t2.ref1 
        AND t.DueDate = t2.DueDate
    CROSS APPLY (SELECT 1 AS N UNION ALL SELECT 2 as N) AS c --used to both both T and T2 SysInvNum's to exclude
    WHERE 1=1
        AND t.LineBalance > 0 AND t2.LineBalance < 0
        AND t.SysInvNum < t2.SysInvNum --make sure the credit came in after the positive SysInvNum 
        AND t.LineBalance = t2.LineBalance * -1
    GROUP BY t.REF1, t.DueDate, abs(t.LineBalance), c.n
    HAVING Count(*) = 1
)
;

DROP TABLE #SysInvTable;