首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >删除给定键的零和行

问删除给定键的零和行
EN

Stack Overflow用户
提问于 2015-01-07 22:40:09
回答 4查看 4.6K关注 0票数 9

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

对于下面的示例数据,我需要做的是从结果集中删除带有73.19和-73.19的两行。因此,如果两行在LineBalance列中具有相同的绝对值,它们的和为0,如果它们在REF1列中具有相同的值,则应该从结果集中删除该值。仍应在结果集中返回具有REF1 = 14598和行馀281.47的行,不应返回REF1 = 14598下面的其他两行。

这样做的目的是“隐藏”会计错误,并对客户进行更正。我的意思是“隐藏”,而不是在他们收到的邮件账单上显示。这里发生的情况是,客户误报了73.19英镑,而当时他们本应收到281.47英镑的账单。我们的AR部门。还了73.19到他们的帐户,并收取了正确的金额为281.47。如您所见,它们都具有相同的REF1值。

​

​

EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2015-01-08 00:24:42

大多数AR/记帐系统将“信用备忘录”(负数)与现金类似,在这种情况下,-73.19将被应用于73.19 LineBalance,就像客户支付了该金额一样,从而产生0美元的余额。

选项1:

您在这个系统中处理现金收据和申请吗?如果是这样的话,您可以从这些现金应用表中提取数据,以显示SysInvNum 3344296和3341758之间的联系。

备选方案2:

我假设PayAdjust列用于减少客户支付后的余额,而LineBalance是一个计算列,即Charges + PayAdjust。

在大多数情况下,AR部门将负责将信用备忘录应用于打开的发票,这样PayAdjust列将在2行之间为0美元,这将导致LineBalance在这2行中的每一行都为0。对于正在使用的系统来说,这可能只是一个培训问题。

这将导致问题中的3行看起来像这样,所以您没有问题,只需将where LineBalance <> 0添加到查询中就可以排除这些行,因为AR部门(它首先应用了信用,因此知道这个问题的答案)明确声明了信用适用于哪个LineBalance:

选项2首选数据结构:

代码语言:javascript
复制
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值不完全匹配。这些都是我为处理边缘情况所做的假设,因此它们可以根据需要进行调整。

代码语言:javascript
复制
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;
票数 3
EN

Stack Overflow用户

发布于 2015-01-08 00:10:20

我将添加一个包含显式标志的字段,告诉您某个电荷是错误/反转错误,然后过滤掉这些行是很简单的。在飞行中做这件事可能会使你的报告很慢。

但是,要解决这个问题,我们可以这样做。该解决方案假定SysInvNum是唯一的。

使用示例数据创建表

代码语言:javascript
复制
DECLARE @T TABLE (SysInvNum int, REF1 int, LineBalance money);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344299, 14602, 558.83);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344298, 14598, 281.47);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344297, 14602, -95.98);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3344296, 14598, -73.19);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (3341758, 14598, 73.19);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (11, 100, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (12, 100, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (13, 100, 50.00);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (21, 200, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (22, 200, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (23, 200, 50.00);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (31, 300, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (32, 300, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (33, 300, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (34, 300, 50.00);

INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (41, 400, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (42, 400, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (43, 400, 50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (44, 400, -50.00);
INSERT INTO @T (SysInvNum, REF1, LineBalance) VALUES (45, 400, 50.00);

我增加了几个有多个错误的案例。

数行和计数行

代码语言:javascript
复制
SELECT
    SysInvNum
    , REF1
    , LineBalance
    , ROW_NUMBER() OVER(PARTITION BY REF1, LineBalance ORDER BY SysInvNum) AS rn
    , COUNT(*) OVER(PARTITION BY REF1, ABS(LineBalance)) AS cc1
FROM @T AS TT

这是结果集:

代码语言:javascript
复制
SysInvNum    REF1    LineBalance    rn    cc1
11           100      50.00         1     3
12           100     -50.00         1     3
13           100      50.00         2     3
21           200     -50.00         1     3
23           200      50.00         1     3
22           200     -50.00         2     3
31           300     -50.00         1     4
32           300      50.00         1     4
33           300     -50.00         2     4
34           300      50.00         2     4
41           400      50.00         1     5
42           400     -50.00         1     5
43           400      50.00         2     5
44           400     -50.00         2     5
45           400      50.00         3     5
3341758      14598    73.19         1     2
3344296      14598   -73.19         1     2
3344298      14598   281.47         1     1
3344297      14602   -95.98         1     1
3344299      14602   558.83         1     1

您可以看到,那些有错误的行的计数> 1。而且,对错误有相同的行号。因此,我们需要删除/隐藏计数>1的行和有两个相同行号的行。

确定要删除的行

代码语言:javascript
复制
WITH
CTE_rn
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , ROW_NUMBER() OVER(PARTITION BY REF1, LineBalance ORDER BY SysInvNum) AS rn
        , COUNT(*) OVER(PARTITION BY REF1, ABS(LineBalance)) AS cc1
    FROM @T AS TT
)
, CTE_ToRemove
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , COUNT(*) OVER(PARTITION BY REF1, rn) AS cc2
    FROM CTE_rn
    WHERE CTE_rn.cc1 > 1
)
SELECT *
FROM CTE_ToRemove
WHERE CTE_ToRemove.cc2 = 2

这是另一个中间结果:

代码语言:javascript
复制
SysInvNum    REF1    LineBalance    cc2
12           100     -50.00         2
11           100      50.00         2
21           200     -50.00         2
23           200      50.00         2
32           300      50.00         2
31           300     -50.00         2
33           300     -50.00         2
34           300      50.00         2
42           400     -50.00         2
41           400      50.00         2
43           400      50.00         2
44           400     -50.00         2
3344296      14598   -73.19         2
3341758      14598    73.19         2

现在,我们把这些都放在一起。

最终查询

代码语言:javascript
复制
WITH
CTE_rn
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , ROW_NUMBER() OVER(PARTITION BY REF1, LineBalance ORDER BY SysInvNum) AS rn
        , COUNT(*) OVER(PARTITION BY REF1, ABS(LineBalance)) AS cc1
    FROM @T AS TT
)
, CTE_ToRemove
AS
(
    SELECT
        SysInvNum
        , REF1
        , LineBalance
        , COUNT(*) OVER(PARTITION BY REF1, rn) AS cc2
    FROM CTE_rn
    WHERE CTE_rn.cc1 > 1
)
SELECT *
FROM @T AS TT
WHERE
    TT.SysInvNum NOT IN 
    (
        SELECT CTE_ToRemove.SysInvNum
        FROM CTE_ToRemove
        WHERE CTE_ToRemove.cc2 = 2
    )
ORDER BY SysInvNum;

结果:

代码语言:javascript
复制
SysInvNum    REF1    LineBalance
13           100       50.00
22           200      -50.00
45           400       50.00
3344297      14602    -95.98
3344298      14598    281.47
3344299      14602    558.83

注意,最后的结果没有REF = 300的任何行,因为有两个更正过的错误,它们完全平衡了彼此。

票数 7
EN

Stack Overflow用户

发布于 2015-01-07 23:08:08

它很笨重,但这可以让你识别出与之匹配的负罪犯。您需要将这两列中的sysInvNum排除在结果集中

代码语言:javascript
复制
Create table #tmp( SysInvNum int,Ref1 int, LineBalance decimal(8,2))

insert into #tmp
values(3344299,14602,558.83)
,(3344298,14598,281.47)
,(3344297,14602,-95.98)
,(3344296,14598,-73.19)
,(3341758,14598,73.19)

--Select * From #tmp

Select * 
INTO #t1
From #tmp
 Where LineBalance < 0

--Select * From #t1

Select SysInvNum1 = #tmp.SysInvNum, SysInvNum2 = #T1.SysInvNum 
INTO #T2
From #tmp 
LEFT JOIN #t1 On #tmp.Ref1 = #T1.Ref1 and #tmp.LineBalance = -#T1.LineBalance
where #t1.SysInvNum is not null


Select * From 
#tmp
Where SysInvNum not in(
Select SysInvNum1 from #t2
union Select SysInvNum2 from #t2
)

drop table #tmp
drop table #t1
drop table #t2
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/27829960

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档