sql-server - 排除具有相同绝对值的负值
问题描述
我有下表
PolicyNumber Premium Commission DataSource EffectiveDate
1 -2.25 -0.41 Internal 31/03/2018
1 160.26 29.14 Internal 31/03/2018
1 98.81 17.96 Internal 31/03/2018
2 361.24 65.67 Internal 31/01/2018
2 570.35 103.70 Internal 31/01/2018
2 -17.05 -3.10 Internal 31/01/2018
3 240.60 0.00 External 31/01/2018
3 196.64 0.00 External 31/01/2018
3 240.60 0.00 External 31/01/2018
3 196.64 0.00 External 31/01/2018
3 -240.60 0.00 External 28/02/2018
3 -196.64 0.00 External 28/02/2018
3 -240.60 0.00 External 28/02/2018
3 -196.64 0.00 External 28/02/2018
3 224.04 107.54 External 28/02/2018
3 176.97 84.95 External 28/02/2018
4 504.00 0.00 External 31/01/2018
4 68.33 0.00 External 31/01/2018
4 504.00 0.00 External 31/01/2018
4 68.33 0.00 External 31/01/2018
4 -504.00 0.00 External 28/02/2018
4 -68.33 0.00 External 28/02/2018
4 -504.00 0.00 External 28/02/2018
4 -68.33 0.00 External 28/02/2018
4 453.55 217.70 External 28/02/2018
4 61.44 29.49 External 28/02/2018
我需要排除与前一个具有相同绝对值的所有负溢价。例如,PolicyNumber 3、Premium 240.60 和 196.64 通过负保费值 -240.60 和 -196.64 进行调整。我需要排除 PolicyNumber 3。
我希望得到如下表所示的最终结果。
PolicyNumber Premium Commission DataSource EffectiveDate
1 -2.25 -0.41 Internal 31/03/2018
1 160.26 29.14 Internal 31/03/2018
1 98.81 17.96 Internal 31/03/2018
2 361.24 65.67 Internal 31/01/2018
2 570.35 103.70 Internal 31/01/2018
2 -17.05 -3.10 Internal 31/01/2018
我已经尝试了下面的查询,但它没有给我我所期望的
with cte as
( select distinct a.PolicyNumber, a.datasource, a.EffectiveDate from InsuranceTable a
inner join (select PolicyNumber, premium, EffectiveDate, datasource
from InsuranceTable) b on a.PolicyNumber = b.PolicyNumber
where a.premium < 0 and a.EffectiveDate = b.EffectiveDate and a.datasource = b.datasource and b.premium > 0 )
select x.PolicyNumber, x.premium, x.EffectiveDate, x.Commission, x.datasource from InsuranceTable x
inner join cte y on x.PolicyNumber = y.PolicyNumber and x.datasource = y.datasource
order by x.PolicyNumber, x.EffectiveDate
我正在使用 Microsoft SQL Server 2012。谢谢大家的帮助。
解决方案
下面的 CTE 发现所有保费都被相同金额的负保费完全抵消。它通过按保单和保费的绝对值汇总来做到这一点。然后,我们只需要一个非常简单的查询就可以获取您想要查看的策略。
WITH cte AS (
SELECT DISTINCT PolicyNumber
FROM InsuranceTable
GROUP BY PolicyNumber, ABS(Premium)
HAVING COUNT(*) > 1 AND MAX(Premium) <> MIN(Premium)
)
SELECT t1.*
FROM InsuranceTable t1
WHERE NOT EXISTS (SELECT 1 FROM cte t2 WHERE t1.PolicyNumber = t2.PolicyNumber);
推荐阅读
- java - 意外的重复键异常
- ruby - 如何修复“错误:无法构建 gem 原生扩展”。在 macOS Mojave 上安装 eventmachine 时
- security - 阻止浏览器告诉我我的本地开发站点很危险?
- azure - 将 request-id(或 operation-id)添加到由 azure 事件网格主题的 Web 挂钩事件订阅发出的 HTTPS 请求
- php - Godaddy CORS 问题 - Angular 7 - Laravel 5
- php - 将 php 文件中的变量显示为 html 文件
- amazon-web-services - 更有效地使用 aws s3 sync?
- kubernetes - 禁止:用户无法获取路径“/”(非匿名用户)
- python-3.x - 如何在本地模拟python3应用程序的应用引擎环境
- javascript - 当我添加两件事时,它会显示 [object Object] [object Object]