我需要对下面的示例数据做的是从结果集中删除带有73.19和-73.19的两行.因此,如果两行在LineBalance列中具有相同的绝对值且它们的总和为0并且如果它们在REF1列中具有相同的值,则应从结果集中删除它们.在结果集中仍应返回REF1 = 14598和行余额281.47的行,并且不应返回REF1 = 14598以下的其他两行.
这一点的意思是“隐藏”会计错误及其对客户的纠正.通过“隐藏”我的意思是,不要在他们收到的邮件中显示它.这里发生的是客户被错误地收费73.19,他们应该收到281.47的账单.所以,我们的AR部门.返回73.19到他们的帐户,并向他们收取正确数额281.47.如您所见,它们都具有相同的REF1值.
解决方法
选项1：
您是否在此系统中处理现金收据和应用程序？如果是这样,您可以从这些现金申请表中提取数据,以显示SysInvNum 3344296和3341758之间的关系.
方案2：
我假设PayAdjust列用于在客户付款后减少余额,而LineBalance是计算列,即Charges PayAdjust.
大多数情况下,AR部门负责将贷项凭证应用于未结发票,以便PayAdjust列在2行之间净赚0美元,这将导致LineBalance每个都为0美元. 2行.它可能只是正在使用的系统的培训问题.
这会导致有问题的3行看起来像这样,所以你没有问题,你只需通过添加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和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,'2014-12-08','Your original test case. This one should stay.'),(3344296,-73.19,null),(3341758,73.19,null)
--.....................................
--Test case 2: How do you match these up?,(2001,2,'2015-01-06','Charge 2.1'),(2002,'Charge 2.2'),(2003,'Charge 2.3'),(2004,'Credit for charge 2.3'),(2005,'Credit for charge 2.1')
--.....................................
--Test case 3,(3001,3,'Charge 3.1'),(3002,'Charge 3.2'),(3003,-146.38,'Credit for charges 3.1 and 3.2')
--.....................................
--Test case 4: Do you hide 4001 or 4002?,(4001,4,'Cable'),(4002,'Internet'),(4003,'Misc Credit')
--.....................................
--Test case 5: remove all lines except the first,(5000,5,9.99,'Charge 5.0 (Should stay)'),(5001,11.11,'Charge 5.1'),(5002,22.22,'Charge 5.2'),(5003,33.33,'Charge 5.3'),(5004,-11.11,'Credit for charge 5.1'),(5005,-33.33,'Credit for charge 5.3'),(5006,-22.22,'Credit for charge 5.2')
--.....................................
--Test case 6: credit occurs before charge,so keep both,(6000,6,'Credit occurs before charge'),(6001,'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;
