我有一个查询,它将导致在我们的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值。

发布于 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首选数据结构:
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;发布于 2015-01-08 00:10:20
我将添加一个包含显式标志的字段,告诉您某个电荷是错误/反转错误,然后过滤掉这些行是很简单的。在飞行中做这件事可能会使你的报告很慢。
但是,要解决这个问题,我们可以这样做。该解决方案假定SysInvNum是唯一的。
使用示例数据创建表
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);我增加了几个有多个错误的案例。
数行和计数行
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这是结果集:
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的行和有两个相同行号的行。
确定要删除的行
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这是另一个中间结果:
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现在,我们把这些都放在一起。
最终查询
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;结果:
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的任何行,因为有两个更正过的错误,它们完全平衡了彼此。
发布于 2015-01-07 23:08:08
它很笨重,但这可以让你识别出与之匹配的负罪犯。您需要将这两列中的sysInvNum排除在结果集中
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 #t2https://stackoverflow.com/questions/27829960
复制相似问题